package gentle.service.impl;

import com.baomidou.mybatisplus.plugins.Page;
import gentle.base.SqlMapper;
import gentle.base.StringUtil;
import gentle.exception.DateTimeKit;
import gentle.service.ICustomerPublicService;
import gentle.shiro.ShiroKit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import gentle.service.ICustomerService;
import gentle.entity.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.commons.lang3.StringUtils;
import gentle.mapper.CustomerDao;
import org.springframework.ui.Model;

@Service
@Transactional
public class CustomerServiceImpl implements ICustomerService {

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    WebSocket webSocket;

    @Autowired
    private CustomerDao dao;

    @Autowired
    private SqlMapper sqlMapper;

    @Override
    public List<CustomerInfo> selectPageListByQueryGetMap(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtils.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtils.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtils.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtils.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtils.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtils.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }

        if (StringUtils.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtils.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartAddTime()) && StringUtils.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtils.isEmpty(searchInfo.getStartAddTime()) && StringUtils.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.id");
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtils.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtils.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtils.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPage(page, params);
    }

    public int getQueryPagesCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtils.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtils.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtils.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtils.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtils.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtils.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtils.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getStartAddTime()) && StringUtils.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtils.isEmpty(searchInfo.getStartAddTime()) && StringUtils.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.id");
        }
        if (StringUtils.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtils.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtils.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtils.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtils.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtils.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        return dao.getQueryPagesCount(page, params);
    }

    public List<ConsultantInfo> total(String startArrivalTime, String endArrivalTime, String oneChannelValue, String doctor,
                                      String twoChannelValue, String thrChannelValue, String fieldTc, String treatmentUnit,
                                      String sreach, String dealStatus, String oppStatus, String abbreviation) {
        String tableName = abbreviation + "customer_info";
        String contitionInfo = " and t.arrival_status!=1 and t.arrival_status!=9 ";
        if (StringUtils.isNotBlank(oppStatus)) {
            if ("1".equals(oppStatus)) {
                contitionInfo += " AND tk.open_id is not null ";
            }
            if ("2".equals(oppStatus)) {
                contitionInfo += " AND tk.open_id is null ";
            }
        }
//        if (!StringUtils.isNotBlank(condition)) {
        contitionInfo += " AND t.customer_status != '作废' ";
//        }
        if (StringUtils.isNotBlank(startArrivalTime)) {
            contitionInfo += " and t.arrival_time >= '" + startArrivalTime + "' ";
        }
        if (StringUtils.isNotBlank(endArrivalTime)) {
            contitionInfo += " and t.arrival_time <= '" + endArrivalTime + "' ";
        }
        if (StringUtils.isNotBlank(fieldTc)) {
            contitionInfo += " and t.field_tc = '" + fieldTc + "' ";
        }
        if (StringUtils.isNotBlank(doctor)) {
            contitionInfo += " and t.doctor = '" + doctor + "' ";
        }
        if (StringUtils.isNotBlank(treatmentUnit)) {
            contitionInfo += " and t.treatment_unit ='" + treatmentUnit + "' ";
        }
        if (StringUtils.isNotEmpty(oneChannelValue)) {
            if (oneChannelValue.indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = oneChannelValue.split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.one_channel in ( '" + oneChannel + "') ";
            }
        }
        if (StringUtils.isNotEmpty(twoChannelValue)) {
            if (twoChannelValue.indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = twoChannelValue.split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.two_channel  in ('" + twoChannel + "') ";
            }
        }
        if (StringUtils.isNotEmpty(thrChannelValue)) {
            if (thrChannelValue.indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = thrChannelValue.split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.thr_channel in ('" + thrChannel + "') ";
            }
        }
        if (StringUtils.isNotBlank(dealStatus)) {
            if ("已结账".equals(dealStatus)) {
                contitionInfo += " and t.deal_status = '已结账' ";
            } else {
                contitionInfo += " and t.deal_status != '已结账' ";
            }
        }
        if (StringUtils.isNotBlank(sreach)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + sreach + "','%') or t.customer_phone like CONCAT('%','" + sreach + "','%') or " +
                    " t.wechat_number like CONCAT('%','" + sreach + "','%') or t.medical_record_number like CONCAT('%','" + sreach + "','%') or t.szm like CONCAT('%" + sreach + "%')) ";
        }
        String sql = "select count(distinct t.reservations_id) as countNum,if(if(sum(actual_account_balance) is null,0,sum(actual_account_balance)) is null,0,if(sum(actual_account_balance) is null,0,sum(actual_account_balance))) as actualAccountBalanceNum from " + tableName + " t  left join members_user tk on t.reservations_id = tk.reservations_id where 1=1 " + contitionInfo +

                " union all select count(distinct t.reservations_id) as countNum,if(sum(actual_account_balance) is null,0,sum(actual_account_balance)) as actualAccountBalanceNum  from " + tableName + " t left join members_user tk on t.reservations_id = tk.reservations_id  where t.customer_status='新客首次' " + contitionInfo +

                " union all select count(distinct t.reservations_id) as countNum ,if(sum(actual_account_balance) is null,0,sum(actual_account_balance)) as actualAccountBalanceNum from " + tableName + " t left join members_user tk on t.reservations_id = tk.reservations_id  where t.customer_status='新客多次' " + contitionInfo +

                " union all select count(distinct t.reservations_id) as countNum,if(sum(actual_account_balance) is null,0,sum(actual_account_balance)) as actualAccountBalanceNum  from " + tableName + " t left join members_user tk on t.reservations_id = tk.reservations_id  where t.customer_status='复诊' " + contitionInfo +

                " union all select count(distinct t.reservations_id) as countNum ,if(sum(actual_account_balance) is null,0,sum(actual_account_balance)) as actualAccountBalanceNum from " + tableName + " t left join members_user tk on t.reservations_id = tk.reservations_id  where t.customer_status='老客'" + contitionInfo +

                //复诊人次
                " union all select count(tt.countNum) as countNum ,count(tt.actualAccountBalanceNum) as actualAccountBalanceNum from " +
                "(select count(distinct t.reservations_id) as countNum ,if(sum(actual_account_balance) is null,0,sum(actual_account_balance)) as actualAccountBalanceNum " +
                " from " + tableName + " t left join members_user tk on t.reservations_id = tk.reservations_id where t.customer_status in ('复诊')  " + contitionInfo + "  group by t.reservations_id, left(arrival_time,10) ) tt";

        List<ConsultantInfo> infoList = sqlMapper.selectList(sql, ConsultantInfo.class);
        return infoList;
    }

    @Override
    public List<CustomerInfo> selectPageQfListByQueryGetMap(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBirthday())) {
            params.put("birthday", "t.birthday like '%-"+searchInfo.getBirthday()+"'");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }

        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.customer_phone");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryQfPage(page, params);
    }

    public int getQueryPagesQfCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        String table2 = tableHead + "customer_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSreach())) {
            params.put("sreach", searchInfo.getSreach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBirthday())) {
            params.put("birthday", "t.birthday like '%-"+searchInfo.getBirthday()+"'");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCondition())) {
            params.put("condition", "");
        } else {
            params.put("condition", "t.arrival_status!=8");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTreatmentUnit())) {
            params.put("treatmentUnit", searchInfo.getTreatmentUnit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDealStatus())) {
            if ("已结账".equals(searchInfo.getDealStatus())) {
                params.put("dealStatus", "t.deal_status ='已结账'");
            } else {
                params.put("dealStatus", "t.deal_status !='已结账'");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartUpdateTime()) && StringUtil.isEmpty(searchInfo.getStartAddTime())) {
            params.put("startUpdateTime", searchInfo.getStartUpdateTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndUpdateTime())) {
            params.put("endUpdateTime", searchInfo.getEndUpdateTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("startAddTime", searchInfo.getStartAddTime());
            params.put("gbtk", "group by tk.reservations_id");
        }
        if (StringUtil.isEmpty(searchInfo.getStartAddTime()) && StringUtil.isEmpty(searchInfo.getStartUpdateTime())) {
            params.put("gbtk", "and t.arrival_status!=1 and t.arrival_status!=9 group by t.customer_phone");
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAddTime())) {
            params.put("endAddTime", searchInfo.getEndAddTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            params.put("doctor", searchInfo.getDoctor());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOppStatus())) {
            if ("1".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is not null");
            } else if ("2".equals(searchInfo.getOppStatus())) {
                params.put("oppStatus", "tk.open_id is null");
            } else {
                params.put("", "");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
            if (searchInfo.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = searchInfo.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " t.one_channel in ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
            if (searchInfo.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = searchInfo.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " t.two_channel in ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
            if (searchInfo.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = searchInfo.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " t.thr_channel in ('" + thrChannel + "')");
            }
        }
        params.put("tableName", table2);
        params.put("tableHead", "tk.abbreviation ='" + tableHead + "'");
        params.put("table1", table1);
        return dao.getQueryPagesQfCount(page, params);
    }

    public List<MembersUser> changeScanCode(String startUpdateTime1, String endUpdateTime1, String startUpdateTime2, String endUpdateTime2,
                                            String startAddTime1, String endAddTime1, String startAddTime2, String endAddTime2, String abbreviation) {
        String sql = "select count(t.reservations_id) as countNum from members_user t where t.abbreviation='" + abbreviation + "' and t.update_time>='" + startUpdateTime1 + "' and t.update_time<='" + endUpdateTime1 + "' " +
                "union all select count(t.reservations_id) as countNum from members_user t where t.abbreviation='" + abbreviation + "' and t.update_time>='" + startUpdateTime2 + "' and t.update_time<='" + endUpdateTime2 + "'" +
                "union all select count(t.reservations_id) as countNum from members_user t where t.abbreviation='" + abbreviation + "' and t.c_time>='" + startAddTime1 + "' and t.c_time<='" + endAddTime1 + "'" +
                "union all select count(t.reservations_id) as countNum from members_user t where t.abbreviation='" + abbreviation + "' and t.c_time>='" + startAddTime2 + "' and t.c_time<='" + endAddTime2 + "' ;";
        List<MembersUser> infoList = sqlMapper.selectList(sql, MembersUser.class);
        return infoList;
    }

    public int getdcsByCustomerId(String id, String abbreviation) {
        String ctable = abbreviation + "customer_info";
        String abbreviationSql = "select t.id from distinction_customer_status t left join " + ctable + " t1" +
                " on t.reservations_id=t1.reservations_id and t.status=1  where t1.id = '" + id + "' limit 1";
        SfInfo info = sqlMapper.selectOne(abbreviationSql, SfInfo.class);
        if (info != null) {
            return 1;
        } else {
            return 0;
        }
    }

    public int getMemberStatus(String reservationsId) {
        String memberSql = "select a.reservations_id,a.open_id,b.open_id as openIdd from members_user a left join open_info b on a.open_id = b.open_id " +
                "where a.reservations_id='" + reservationsId + "' limit 1";
        MembersUser membersUser = sqlMapper.selectOne(memberSql, MembersUser.class);
        if (membersUser == null) {
            return 1;
        } else {
            if (membersUser.getOpenId() == null || "".equals(membersUser.getOpenId()) || "null".equals(membersUser.getOpenId())
                    || membersUser.getOpenIdd() == null || "".equals(membersUser.getOpenIdd()) || "null".equals(membersUser.getOpenIdd())) {
                return 1;
            } else {
                return 0;
            }
        }
    }

    // 复诊 复诊到店 /新建预约 / 预约视图再预约
    public MembersArriveInfo addCustomerFzs(String customerPhone, String projectData, String treatmentUnit, String fieldTc, String doctor, String remarks,
                                            String reservationsId, String isFz, String startTimeValue, String duration, String customerId, String abbreviation,
                                            String userName, String outpatientName, String userId) throws ParseException {
        String ctable = abbreviation + "customer_info";
        //设置黄页编号
        String yellowNumbers = "0000123";
        String medicalRecordNumber = "";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = formatter.parse(startTimeValue);
        Date date1 = new Date(date.getTime() + Integer.parseInt(duration) * 60 * 1000);
        String endTimeValue = formatter.format(date1);
        MembersArriveInfo membersArriveInfos = new MembersArriveInfo();
        String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中'  " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话'  " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName," +
                "a.* from  " + ctable + " a where id = '" + customerId + "' and reservations_id='" + reservationsId + "'";
        CustomerInfo customerIn = sqlMapper.selectOne(selsql, CustomerInfo.class);
        if (customerIn == null) {       // 去另外一家店复诊,查到去的哪家店
            yellowNumbers = customerPublicService.getMaxYellowNumber(abbreviation);
            String sqld = "select id,abbreviation from distinction_customer_status where reservations_id ='" + reservationsId + "' and status=1 order by add_time limit 0,1 ";
            Dept cinfo = sqlMapper.selectOne(sqld, Dept.class);
            String sql2 = " select id,reservations_id,customer_name,customer_phone,medical_record_number  as medicalRecordNumber " +
                    " from " + cinfo.getAbbreviation() + "customer_info t where reservations_id ='" + reservationsId + "' " +
                    " order by arrival_time desc limit 0,1 ";
            customerIn = sqlMapper.selectOne(sql2, CustomerInfo.class);
            medicalRecordNumber = customerIn.getMedicalRecordNumber(); //继承上一家的病历号
            customerIn.setAbbreviation(cinfo.getAbbreviation());
            //去另外一家店复诊，记录下
            String sql = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                    " values('" + customerIn.getReservationsId() + "','" + customerIn.getCustomerName() + "','" + customerIn.getCustomerPhone()
                    + "','" + outpatientName + "','" + abbreviation + "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + medicalRecordNumber + "') ";
            sqlMapper.insert(sql);
        } else {
            medicalRecordNumber = customerIn.getMedicalRecordNumber();
            yellowNumbers = customerIn.getYellowNumber();
        }
        if (startTimeValue.compareTo((DateTimeKit.now())) < 0) {
            membersArriveInfos.setIds("11");
            return membersArriveInfos;
        } else {
            String ctablefom = abbreviation + "customer_info";
            if (StringUtil.isNotEmpty(customerIn.getAbbreviation())) {
                ctablefom = customerIn.getAbbreviation() + "customer_info";
            }
            Double moneyBalance = 0.00;
            Double jfbalance = 0.00;
            String getMoneyBalance = "select sum(a.actual_account_balance) as moneyBalance from reservemoney_deal a " +
                    " where a.reservations_id='" + reservationsId + "' " +
                    " and a.add_store='" + outpatientName + "'";
            CustomerInfo customerInfo1 = sqlMapper.selectOne(getMoneyBalance, CustomerInfo.class);
            if (customerInfo1 != null) {
                moneyBalance = customerInfo1.getMoneyBalance();
            }
            String getjFBalance = "select sum(a.score_balance) as jfbalance from score_deal a " +
                    " where a.reservations_id='" + reservationsId + "' " +
                    " and a.add_store='" + outpatientName + "'";
            CustomerInfo customerInfo2 = sqlMapper.selectOne(getjFBalance, CustomerInfo.class);
            if (customerInfo2 != null) {
                jfbalance = customerInfo2.getJfbalance();
            }
            String id = customerPublicService.getId(abbreviation);
            String sql = "INSERT INTO " + ctable + " (`id`,scarchiving,`diagnosis_status`,`first_visit_date`,`actual_account_balance`," +
                    "`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`,`appointment_pay_service`," +
                    "`arrival_store`,`phone_shar_ship`,`deal_status`, `status`,`customer_name`, `pseudonym`,szm, " +
                    "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                    " `customer_sex`,customer_sex_head, `customer_age`, `birthday`,  `address`, `referee`, `referee_ship`, `id_card_no`," +
                    " `fixed_telephone`, `is_have_children`, `email`, `zip_code`,treatment_unit, " +
                    "  `number_of_audits`, `auditor_time`, `final_auditor`,`yhbalance`,`jfbalance`,`money_balance`," +
                    "  `non_transaction_level`, `transaction_level`, `characters`, " +
                    "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                    " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                    " `province`, `cities`, `region`, `medical_record_number`, " +
                    " `marital_status`, `occupation`, `archiving`, `entry_clerk` ,`update_people`," +
                    "  `filing_time`," +
                    " `customer_phone`,customer_phone2,`field_tc`,`query_tc`,`doctor`,`remarks`,`remarks_reservation`,`start_time`,`end_time`,create_appointment_time,yellow_number )  " +
                    " select '" + id + "',scarchiving,'未看诊',first_visit_date,actual_account_balance," +
                    " give_account_balance,account_arrears, " +
                    "(case " +
                    " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<10) then '0-9' " +
                    " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<20) then '10-19' " +
                    " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<30) then '20-29' " +
                    " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<40) then '30-39' " +
                    " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<50) then '40-49' " +
                    " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<60) then '50-59' " +
                    " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<70) then '60-69' " +
                    " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<80) then '70-79' " +
                    " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<90) then '80-89' " +
                    " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<100) then '90-99' " +
                    " else '99+' end)," +
                    " reservations_id,'" + projectData + "'," +
                    " '" + outpatientName + "',phone_shar_ship,'未开单','预约',customer_name , pseudonym, szm, " +
                    "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                    " customer_sex,customer_sex_head, (DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')), birthday, address, referee, referee_ship, id_card_no," +
                    " fixed_telephone, is_have_children, email, zip_code,'" + treatmentUnit + "'," +
                    "   number_of_audits, auditor_time, final_auditor,0," + jfbalance + "," + moneyBalance + "," +
                    " non_transaction_level,transaction_level,characters," +
                    " consumption_habits,is_return_visit,cause_of_no_return_visit,consum_ability," +
                    " qq_number,wechat_number,other_contact,country," +
                    " province, cities,region,'" + medicalRecordNumber + "',marital_status," +
                    " occupation,'" + userName + "','" + userName + "','" + userName + "', '" + DateTimeKit.now() + "'," +
                    " '" + customerPhone + "',customer_phone2,'" + fieldTc + "','" + fieldTc + "','" + doctor + "'," +
                    "remarks,'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "','" + yellowNumbers + "' from " + ctablefom
                    + " where id='" + customerId + "' ";
            sqlMapper.insert(sql);
            //根据预约ID 查询最近一次到店患者 继承转诊记录
            String sql1 = " select * from " + ctable + " t where arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null " +
                    " and reservations_id ='" + reservationsId + "' order by arrival_time desc limit 0,1 ";
            CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
            if (info != null) {
                if (info.getReferralerPerson() == null || info.getReferralerPerson() == "" || info.getReferralerPersoned() == null || info.getReferralerPersoned() == "" || info.getReferralerTime() == null || info.getReferralerTime() == "" || info.getReferralerRemarks() == null || info.getReferralerRemarks() == "") {
                    info.setReferralerPerson("null");
                    info.setReferralerPersoned("null");
                    info.setReferralerRemarks("null");
                    info.setReferralerTime(null);
                } else {
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                    Date dt1 = null;
                    Date dt2 = null;
                    try {
                        dt1 = df.parse(DateTimeKit.now().substring(0, 10));
                        dt2 = df.parse(info.getReferralerTime().substring(0, 10));
                        if (dt1.getTime() - dt2.getTime() > 30 * 24 * 60 * 60 * 1000L) {
                            info.setReferralerPerson("null");
                            info.setReferralerPersoned("null");
                            info.setReferralerRemarks("null");
                            info.setReferralerTime(null);
                        } else {
                            info.setReferralerPerson(info.getReferralerPerson());
                            info.setReferralerPersoned(info.getReferralerPersoned());
                            info.setReferralerTime(info.getReferralerTime());
                            info.setReferralerRemarks(info.getReferralerRemarks());
                        }
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                String referralerPerson = "";
                String referralerPersoned = "";
                String referralerTime = "";
                String referralerRemarks = "";
                if (StringUtil.isNotEmpty(info.getReferralerPerson())) {
                    referralerPerson = "'" + info.getReferralerPerson() + "'";
                } else {
                    referralerPerson = "null";
                }
                if (StringUtil.isNotEmpty(info.getReferralerPersoned())) {
                    referralerPersoned = "'" + info.getReferralerPersoned() + "'";
                } else {
                    referralerPersoned = "null";
                }
                if (StringUtil.isNotEmpty(info.getReferralerTime())) {
                    referralerTime = "'" + info.getReferralerTime() + "'";
                } else {
                    referralerTime = null;
                }
                if (StringUtil.isNotEmpty(info.getReferralerRemarks())) {
                    referralerRemarks = "'" + info.getReferralerRemarks() + "'";
                } else {
                    referralerRemarks = "null";
                }
                String updateSql = "update " + ctable + " a set a.referraler_person=" + referralerPerson + ",a.referraler_personed=" + referralerPersoned + "," +
                        " a.referraler_time=" + referralerTime + ",a.referraler_remarks=" + referralerRemarks + " where id='" + id + "' ";
                sqlMapper.update(updateSql);
            }
            //插入视图改动日志 复诊预约
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userName + "','新增预约','" + userId + "','" + customerIn.getCustomerStatus() + "', '"
                    + customerIn.getCustomerName() + "', '" + customerIn.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约项目：" + projectData + " </br>\n预约患者：" + customerIn.getCustomerName() + " </br>\n预约时间：" + startTimeValue.substring(0, 16) + " - " + endTimeValue.substring(11, 16) + " " +
                    "</br>\n预约状态：预约 </br>\n预约科室：" + treatmentUnit + " </br>\n预约医生：" + doctor + " </br>\n咨询师：" + fieldTc + " </br>\n备注：" + remarks + "','" + id + "'," +
                    "'" + customerIn.getReservationsId() + "');";
            sqlMapper.insert(sq11);
            if ("1".equals(isFz)) {  //1：复诊到店，直接改为到店状态 2：复诊预约 ，点复诊状态都为复诊
                String sqlst = "select id from distinction_customer_status " +
                        " where reservations_id='" + reservationsId + "' and status=1 and left(add_time,10)<left(now(),10) ; ";
                List<CustomerInfo> list = sqlMapper.selectList(sqlst, CustomerInfo.class);
                String customerStatus = "";   //患者之前来过
                if (list.size() > 0) {
                    String sqlst1 = "select id from distinction_customer_status " +
                            " where reservations_id='" + reservationsId + "' and status=1  " +
                            " and deal_status='已成交' and left(add_time,10)<left(now(),10) ; ";
                    List<CustomerInfo> list11 = sqlMapper.selectList(sqlst1, CustomerInfo.class);
                    if (list11.size() > 0) {   //患者之前来过 ，成交过
                        customerStatus = "复诊";
                    } else {    //患者之前来过 ，没有成交过
                        String sqld1 = "select id from " + ctable + " where " +
                                " reservations_id='" + reservationsId + "' and customer_status='新客多次' and left(arrival_time,10)<left(now(),10) ";
                        List<CustomerInfo> list1 = sqlMapper.selectList(sqld1, CustomerInfo.class);
                        if (list1.size() > 0) {
                            customerStatus = "新客N次";
                        } else {
                            customerStatus = "新客多次";
                        }
                    }
                } else {  //没来过，新客
                    customerStatus = "新客首次";
                }
                String sql3 = "update " + ctable + " a set a.status='到店',customer_status='" + customerStatus + "'," +
                        "arrival_time='" + DateTimeKit.now() + "'," +
                        "arrival_status=2,add_status=0 where id='" + id + "'";
                sqlMapper.update(sql3);
            }
            String sql2 = "update " + ctable + " a set a.add_status=1 where id='" + customerId + "'";
            sqlMapper.update(sql2);
            webSocket.sendMessageViews("新增预约", abbreviation);
            membersArriveInfos = this.memberDistinguishInUp(reservationsId, abbreviation);
            membersArriveInfos.setIds("1");
            return membersArriveInfos;
        }
    }

    public MembersArriveInfo memberDistinguishInUp(String reservationsId, String abbreviation) {
        String ctable = abbreviation + "customer_info";
        String sssql = "select quality from member_distinguish_info where reservations_id='" + reservationsId + "'";
        MembersArriveInfo membersArriveInfo = sqlMapper.selectOne(sssql, MembersArriveInfo.class);
        String sssql1 = "select * from " + ctable + " where reservations_id='" + reservationsId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sssql1, CustomerInfo.class);
        String membersImageBs = ShiroKit.getSessionAttr("membersImageBs");
        Base64.Decoder decoder = Base64.getDecoder();
        if (membersImageBs != null) {
            byte[] b = decoder.decode(membersImageBs);
            // 处理数据
            for (int i = 0; i < b.length; ++i) {
                if (b[i] < 0) {
                    b[i] += 256;
                }
            }
            try {
                String iconName = ShiroKit.getSessionAttr("membersId") + ".jpg";
                String httpUrl = "D:\\8068\\apache-tomcat-8.0.30\\webapps\\ROOT\\memberTouxiang\\" + iconName + "";
                OutputStream out = new FileOutputStream(httpUrl);
                out.write(b);
                out.flush();
                out.close();
                String avatarUrl = "http://www.yiyatest.cn:8068/memberTouxiang/" + iconName + "";
                /* 找出指定的2个字符在 该字符串里面的 位置 */
                if (membersArriveInfo == null) {
                    if (ShiroKit.getSessionAttr("membersFeature") != null) {
                        String sql = "INSERT INTO `member_distinguish_info` (`reservations_id`, `medical_record_number`, `member_name`, `member_phone`, " +
                                "`member_age`, `member_sex`, `avatar`, `feature`, `quality`,member_id) VALUES " +
                                "('" + customerInfo.getReservationsId() + "','" + customerInfo.getMedicalRecordNumber() + "', '" + customerInfo.getCustomerName() + "'," +
                                " '" + customerInfo.getCustomerPhone() + "', " + customerInfo.getCustomerAge() + ", " + customerInfo.getCustomerSex() + ", " +
                                " '" + avatarUrl + "', '" + ShiroKit.getSessionAttr("membersFeature") + "'," +
                                " '" + ShiroKit.getSessionAttr("membersQuality") + "', '" + ShiroKit.getSessionAttr("membersId") + "');";
                        sqlMapper.insert(sql);
                    }
                } else {
                    if (ShiroKit.getSessionAttr("quality") != null) {
                        if (membersArriveInfo.getQuality() < Integer.parseInt(ShiroKit.getSessionAttr("quality"))) {
                            String usql = "update `member_distinguish_info` set quality='" + ShiroKit.getSessionAttr("quality") + "' where reservations_id='" + reservationsId + "'";
                            sqlMapper.update(usql);
                        }
                    }
                }
                ShiroKit.setSessionAttr("membersFeature", null);
                ShiroKit.setSessionAttr("membersImageBs", null);
                ShiroKit.setSessionAttr("membersQuality", null);
                ShiroKit.setSessionAttr("membersId", null);
            } catch (IOException e) {
                e.printStackTrace();
                String sssql2 = "select * from member_distinguish_info where reservations_id='" + reservationsId + "'";
                MembersArriveInfo membersArriveInfo2 = sqlMapper.selectOne(sssql2, MembersArriveInfo.class);
                membersArriveInfo2.setStatus(1);
                return membersArriveInfo2;
            } finally {
                String sssql2 = "select * from member_distinguish_info where reservations_id='" + reservationsId + "'";
                MembersArriveInfo membersArriveInfo2 = sqlMapper.selectOne(sssql2, MembersArriveInfo.class);
                membersArriveInfo2.setStatus(1);
                return membersArriveInfo2;
            }
        } else {
            MembersArriveInfo membersArriveInfo2 = new MembersArriveInfo();
            membersArriveInfo2.setStatus(2);
            return membersArriveInfo2;
        }
    }

    public List<DiagnoseProjectInfo> getDiagnoseInfo(String projectCategory) {
        if (StringUtils.isNotBlank(projectCategory)) {
            String selectSql = "select id,add_name as addName,project_category as projectCategory,project_name as projectName ," +
                    "create_time as createTime,update_time as updateTime from diagnose_project_info " +
                    "where 1=1 and project_category = '" + projectCategory + "' ";
            List<DiagnoseProjectInfo> list = sqlMapper.selectList(selectSql,DiagnoseProjectInfo.class);
            return list;
        } else {
            String selectSql = "select id,add_name as addName,project_category as projectCategory,project_name as projectName ," +
                    "create_time as createTime,update_time as updateTime from diagnose_project_info;";
            List<DiagnoseProjectInfo> list = sqlMapper.selectList(selectSql,DiagnoseProjectInfo.class);
            return list;
        }
    }

    public String getDoctorStatus(String doctor, String value, String outpatientName){
        String sql = "select t.status from doctor_scheduling t where t.doctor_name='" + doctor + "' and left(t.the_date,10)='" + value.substring(0, 10) + "' and t.outpatient_name='" +outpatientName+ "' limit 1";
        DoctorLookedInfo doctorLookedInfo = sqlMapper.selectOne(sql, DoctorLookedInfo.class);
        if (doctorLookedInfo == null) {
            return "1";
        } else {
            return doctorLookedInfo.getStatus();
        }
    }

    // 重咨 重咨到店
    public MembersArriveInfo addNatureCustomerCZ(String reservationsId, String customerName, String treatmentUnit,
                                                 String fieldTc, String remarks, String abbreviation, String outpatientName,String userName) throws ParseException {
        String ctable = abbreviation + "customer_info";
        String sql = "select id from " + ctable + " where reservations_id ='" + reservationsId + "' " +
                " and left(arrival_time,10)=left(now(),10) and treatment_unit='" + treatmentUnit + "'" +
                " and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10" +
                " and customer_status!='复诊' and arrival_time is not null and query_tc!='分诊TC' and query_tc!='待定TC' limit 1";
        CustomerInfo customerInfos = sqlMapper.selectOne(sql, CustomerInfo.class);
        MembersArriveInfo membersArriveInfos = new MembersArriveInfo();
        if (customerInfos != null) {
            membersArriveInfos.setIds("0");
            return membersArriveInfos;
        } else {
            String sqlseltc = "select id from " + ctable + " where reservations_id ='" + reservationsId + "' " +
                    " and left(arrival_time,10)=left(now(),10) and query_tc!='" + fieldTc + "'" +
                    " and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 " +
                    " and customer_status!='复诊' and arrival_time is not null and query_tc!='分诊TC' and query_tc!='待定TC' limit 1";
            CustomerInfo customerInfoseltc = sqlMapper.selectOne(sqlseltc, CustomerInfo.class);
            if (customerInfoseltc != null && !"待定TC".equals(fieldTc)) {
                membersArriveInfos.setIds("2");
            } else {
                String sql1 = " select * from " + ctable + " t where arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null " +
                        " and reservations_id ='" + reservationsId + "' order by arrival_time desc limit 0,1 ";
                CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
                if (customerInfo == null) {   //去另外一家店复诊,查到去的哪家店
                    String sqld = "select id,abbreviation from distinction_customer_status where reservations_id ='" + reservationsId + "' and status=1  order by add_time desc limit 0,1 ";
                    Dept cinfo = sqlMapper.selectOne(sqld, Dept.class);
                    String sql2 = " select * from " + cinfo.getAbbreviation() + "customer_info t where reservations_id ='" + reservationsId + "' order by arrival_time desc limit 0,1 ";
                    customerInfo = sqlMapper.selectOne(sql2, CustomerInfo.class);
                    customerInfo.setAbbreviation(cinfo.getAbbreviation());
//                    customerInfo.setMedicalRecordNumber(customerInfo.getMedicalRecordNumber());  //继承上一家的病历号
                    customerInfo.setYellowNumber(customerPublicService.getMaxYellowNumber(abbreviation));
                    String sqlin = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                            " values('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone()
                            + "','" + outpatientName + "','" + abbreviation + "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + customerInfo.getMedicalRecordNumber() + "') ";
                    sqlMapper.insert(sqlin);//去另外一家店复诊，记录下
                } else {
                    if (customerInfo.getReferralerPerson() == null || customerInfo.getReferralerPerson() == "") {
                        customerInfo.setReferralerPerson("");
                    } else if (customerInfo.getReferralerPersoned() == null || customerInfo.getReferralerPersoned() == "") {
                        customerInfo.setReferralerPersoned("");
                    } else if (customerInfo.getReferralerTime() == null || customerInfo.getReferralerTime() == "") {
                        customerInfo.setReferralerTime(null);
                    } else if (customerInfo.getReferralerRemarks() == null || customerInfo.getReferralerRemarks() == "") {
                        customerInfo.setReferralerRemarks("");
                    } else {
                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                        Date dt1 = df.parse(DateTimeKit.now().substring(0, 10));
                        Date dt2 = df.parse(customerInfo.getReferralerTime().substring(0, 10));
                        if (dt1.getTime() - dt2.getTime() > 30 * 24 * 60 * 60 * 1000L) {
                            customerInfo.setReferralerPerson("");
                            customerInfo.setReferralerPersoned("");
                            customerInfo.setReferralerRemarks("");
                            customerInfo.setReferralerTime(null);
                        } else {
                            customerInfo.setReferralerPerson(customerInfo.getReferralerPerson());
                            customerInfo.setReferralerPersoned(customerInfo.getReferralerPersoned());
                            customerInfo.setReferralerTime(customerInfo.getReferralerTime());
                            customerInfo.setReferralerRemarks(customerInfo.getReferralerRemarks());
                        }
                    }
                    customerInfo.setMedicalRecordNumber(customerInfo.getMedicalRecordNumber());
                    customerInfo.setYellowNumber(customerInfo.getYellowNumber());
                }
                customerInfo.setCustomerId(customerInfo.getId());
                customerInfo.setStartTime(customerPublicService.getStartTime());
                customerInfo.setEndTime(customerPublicService.getEndTime());
                customerInfo.setCustomerName(customerName);
                customerInfo.setTreatmentUnit(treatmentUnit);
                customerInfo.setFieldTc(fieldTc);
                customerInfo.setRemarks(remarks+customerInfo.getRemarks());
                customerInfo.setReservationsId(customerInfo.getReservationsId());
                customerInfo.setCustomerStatus(customerPublicService.getNewCustomerStatus(reservationsId,abbreviation));
                customerInfo.setId(customerPublicService.getId(abbreviation));
                customerInfo.setDoctor("未指定医生");
                customerInfo.setMarketFilingTime(customerInfo.getMarketFilingTime());
                this.insertReturncz(customerInfo,abbreviation,outpatientName,userName);
                membersArriveInfos = this.memberDistinguishInUp(reservationsId,abbreviation);
                membersArriveInfos.setIds("1");
            }
            return membersArriveInfos;
        }
    }

    public int insertReturncz(CustomerInfo customerInfo, String abbreviation, String outpatientName,String userName) {
        String ctable = abbreviation + "customer_info";
        String ctablefom = abbreviation + "customer_info";
        Double moneyBalance = 0.00;
        Double jfbalance = 0.00;
        if (StringUtil.isNotEmpty(customerInfo.getAbbreviation())) {
            ctablefom = customerInfo.getAbbreviation() + "customer_info";
        }
        String getMoneyBalance = "select sum(a.actual_account_balance) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' " +
                " and a.add_store='" + outpatientName + "'";
        CustomerInfo customerInfo1 = sqlMapper.selectOne(getMoneyBalance, CustomerInfo.class);
        if (customerInfo1 != null) {
            moneyBalance = customerInfo1.getMoneyBalance();
        }
        String getjFBalance = "select sum(a.score_balance) as jfbalance from score_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' ";
        CustomerInfo customerInfo2 = sqlMapper.selectOne(getjFBalance, CustomerInfo.class);
        if (customerInfo2 != null) {
            jfbalance = customerInfo2.getJfbalance();
        }
        String referralerPerson = "";
        String referralerPersoned = "";
        String referralerTime = "";
        String referralerRemarks = "";
        String marketFilingTime = "";
        if (customerInfo.getMarketFilingTime() == null || customerInfo.getMarketFilingTime() == "") {
            marketFilingTime = "null";
        } else {
            marketFilingTime = "'" + customerInfo.getMarketFilingTime() + "'";
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerPerson())) {
            referralerPerson = "'" + customerInfo.getReferralerPerson() + "'";
        } else {
            referralerPerson = "null";
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerPersoned())) {
            referralerPersoned = "'" + customerInfo.getReferralerPersoned() + "'";
        } else {
            referralerPersoned = "null";
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerTime())) {
            referralerTime = "'" + customerInfo.getReferralerTime() + "'";
        } else {
            referralerTime = null;
        }
        if (StringUtil.isNotEmpty(customerInfo.getReferralerRemarks())) {
            referralerRemarks = "'" + customerInfo.getReferralerRemarks() + "'";
        } else {
            referralerRemarks = "null";
        }
        String sql = "INSERT INTO `" + ctable + "` (`id`,`first_visit_date`," +
                "`age_range`,`reservations_id`,`appointment_pay_service`,`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, " +
                "`pseudonym`, `customer_phone`,customer_phone2, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "`field_tc`,`query_tc`, `customer_sex`, customer_sex_head,`customer_age`, `birthday`," +
                " `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                "`customer_status`, `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                "`actual_account_balance`,`give_account_balance` ,`account_arrears`,`receivables`,`cash_receipts`,`yhbalance`,`jfbalance`,`money_balance`," +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk`,`update_people`,`update_time`," +
                " `filing_time`, `arrival_time`, `remarks`, `status`, `diagnosis_status`,`arrival_status`,`doctor`,`start_time`,`end_time`," +
                "`create_appointment_time`,yellow_number,xcustomer_status,scarchiving,treatment_unit,szm,referraler_person,referraler_personed,referraler_time,referraler_remarks,market_filing_time) " +
                " select '" + customerInfo.getId() + "',first_visit_date," +
                "(case " +
                " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<10) then '0-9' " +
                " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<20) then '10-19' " +
                " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<30) then '20-29' " +
                " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<40) then '30-39' " +
                " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<50) then '40-49' " +
                " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<60) then '50-59' " +
                " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<70) then '60-69' " +
                " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<80) then '70-79' " +
                " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<90) then '80-89' " +
                " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')+0)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)), '%Y')+0)<100) then '90-99' " +
                " else '99+' end)," +
                "reservations_id," +
                " '','" + outpatientName+ "',phone_shar_ship,'未开单','" + customerInfo.getCustomerName() + "'," +
                "pseudonym,customer_phone,customer_phone2,`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "'" + customerInfo.getFieldTc() + "','" + customerInfo.getFieldTc() + "', `customer_sex`, customer_sex_head,(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)), '%Y')), `birthday`," +
                " `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`, " +
                " '" + customerInfo.getCustomerStatus() + "', `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, '" + customerInfo.getMedicalRecordNumber() + "', " +
                "`actual_account_balance`,`give_account_balance` ,`account_arrears`,`receivables`,`cash_receipts`,0," + jfbalance + "," + moneyBalance + "," +
                " `marital_status`, `occupation`,'" + userName + "','" + userName + "','" + userName + "','" + DateTimeKit.now() + "'," +
                " `filing_time`, '" + DateTimeKit.now() + "','" + customerInfo.getRemarks() + "', `status`, '未看诊',2,'" + customerInfo.getDoctor() + "'," +
                "'" + customerInfo.getStartTime() + "','" + customerInfo.getEndTime() + "'," +
                "'" + DateTimeKit.now() + "','" + customerInfo.getYellowNumber() + "','复到','" + customerInfo.getScarchiving() + "','" + customerInfo.getTreatmentUnit() + "',firstconcats(customer_name) ," + referralerPerson + "," + referralerPersoned + "," + referralerTime + "," + referralerRemarks + "," + marketFilingTime + " " +
                "from " + ctablefom + " where id ='" + customerInfo.getCustomerId() + "' ;";
        return sqlMapper.insert(sql);
    }

    public int updateCustomeInfoValue(CustomerInfo info){
        String ctable = info.getAbbreviation() + "customer_info";
        String mtable = info.getAbbreviation() + "medicalrecord_info";
        String dtable = info.getAbbreviation() + "deal_info";
        String userName = info.getUserName();
        String userId = info.getUserId();
        String outpatientName = info.getOutpatientName();

        String sql11 = "select count(1) as amount from " + ctable + " where upper(medical_record_number)=upper('" + info.getMedicalRecordNumber() + "') " +
                " and reservations_id !='" + info.getReservationsId() + "' ";
        CustomerInfo customer = sqlMapper.selectOne(sql11, CustomerInfo.class);
        String sql1 = "select id,reservations_id,one_medium,two_medium,thr_medium,medical_record_number from " + ctable + " where id='" + info.getId() + "'";
        CustomerInfo ccinfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        if (customer.getAmount() > 0 && !info.getMedicalRecordNumber().equals(ccinfo.getMedicalRecordNumber())) {
            return 0;
        } else {
            String sql = "update " + ctable + " set customer_name ='" + info.getCustomerName() + "'" +
                    ",birthday='" + info.getBirthday() + "'" +
                    ",age_range= " +
                    "(case " +
                    " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<10) then '0-9' " +
                    " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<20) then '10-19' " +
                    " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<30) then '20-29' " +
                    " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<40) then '30-39' " +
                    " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<50) then '40-49' " +
                    " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<60) then '50-59' " +
                    " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<70) then '60-69' " +
                    " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<80) then '70-79' " +
                    " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<90) then '80-89' " +
                    " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<100) then '90-99' " +
                    " else '99+' end)" +
                    ",customer_age='" + info.getCustomerAge() + "',customer_sex='" + info.getCustomerSex() + "',customer_sex_head='"+customerPublicService.getSexPt(info)+"'" +
                    ",medical_record_number='" + info.getMedicalRecordNumber() + "',referee='" + info.getReferee() + "',referee_ship='" + info.getRefereeShip() + "'" +
                    ",province='" + info.getProvince() + "',cities='" + info.getCities() + "',region='" + info.getRegion() + "',address='" + info.getAddress() + "'" +
                    ",occupation='" + info.getOccupation() + "',consum_ability='" + info.getConsumAbility() + "',consumption_habits='" + info.getConsumptionHabits() +
                    "',id_card_no='" + info.getIdCardNo() + "',fixed_telephone='" + info.getFixedTelephone() + "',yellow_number='" + info.getYellowNumber() + "',one_medium='" + info.getOneMedium() + "'" +
                    ",two_medium='" + info.getTwoMedium() + "',thr_medium='" + info.getThrMedium() + "',qq_number='" + info.getQqNumber() +
                    "',wechat_Number='" + info.getWechatNumber() + "',email='" + info.getEmail() + "',zip_code='" + info.getZipCode() +
                    "',marital_status='" + info.getMaritalStatus() + "',is_have_children='" + info.getIsHaveChildren() + "',characters='" + info.getCharacters() +
                    "',remarks='" + info.getRemarks() + "' where reservations_id ='" + info.getReservationsId() + "';";
            String upsql = "update " + ctable + " set " +
                    "update_people='" + userName + "',update_time='" + DateTimeKit.now() + "' where id ='" + info.getId() + "';";
            String updelsql = "update " + dtable + " set orthodontic_number='" + info.getOrthodonticNumber() + "' " +
                    " where reservations_id ='" + info.getReservationsId() + "' and orthodontic_number='" + info.getOrthodonticNumberValue() + "';";
            if (info.getIsUpdaMediu() == 2) { //插入媒介修改日志
                String sq13 = "INSERT INTO `medium_log_info` ( `customer_name`,`customer_phone`,`person`, `createtime`,`one_medium2`,`two_medium2`,`thr_medium2`," +
                        "one_medium1,two_medium1,thr_medium1) VALUES " +
                        " ( '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + userName + "','" + DateTimeKit.now() + "'," +
                        "'" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() +
                        "', '" + ccinfo.getOneMedium() + "', '" + ccinfo.getTwoMedium() + "','" + ccinfo.getThrMedium() + "');";
                sqlMapper.insert(sq13);
            }
            if (!info.getMedicalRecordNumber().equals(ccinfo.getMedicalRecordNumber())) {
                //插入号修改日志
                String sq11 = "INSERT INTO `customer_medical_number_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                        " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                        " ('" + DateTimeKit.now() + "','" + userName + "','修改','" + userId + "','" + info.getCustomerStatus() + "', '"
                        + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + outpatientName +
                        "','" + userName + " :修改了 " + info.getCustomerName() + " 的病历号 ','" + info.getId() + "','" + info.getReservationsId() + "');";
                sqlMapper.insert(sq11);
                String sqlupodamesql1 = "update " + mtable+ " a set customer_phone=null where customer_phone='" + info.getCustomerPhone() + "'";
                sqlMapper.update(sqlupodamesql1);
                String sqlupodamesql2 = "update " + mtable + " a set customer_phone='" + info.getCustomerPhone() + "' where CONCAT(a.prefix_part_one,a.prefix_part_two,a.num)='" + info.getMedicalRecordNumber() + "'";
                sqlMapper.update(sqlupodamesql2);
                String updsql1 = "update distinction_customer_status set medical_record_number='" + info.getMedicalRecordNumber() + "' where reservations_id='" + ccinfo.getReservationsId() + "' and status=1 ";
                sqlMapper.update(updsql1);
                String updsql2 = "update reservations_info set identification_number='" + info.getMedicalRecordNumber() + "' where id='" + ccinfo.getReservationsId() + "'";
                sqlMapper.update(updsql2);
                String updsql3 = "update reservations_infosc set identification_number='" + info.getMedicalRecordNumber() + "' where id='" + ccinfo.getReservationsId() + "'";
                sqlMapper.update(updsql3);
            }
            sqlMapper.update(upsql);
            sqlMapper.update(updelsql);
            return sqlMapper.update(sql);
        }
    }

    public List<PayServicedInfo> unclosedRecords(String reservationsId, String abbreviation, String areaAbbreviation) {
        String dtable=abbreviation+"deal_info";
        String ptable=areaAbbreviation+"pay_service_info";
        //成交记录 ,结账记录
        String sqlno = "SELECT t1.id AS id,t1.customer_id AS customerId,t1.reservations_id AS reservationsId,t1.tc_name as tcName ,t1.outpatient_name AS outpatientName," +
                " t1.deal_status AS dealStatus, left(t1.get_time,19) as getTime,t1.operation_time as operationTime, sum(t1.service_actual_pay) as serviceActualPay, " +
                " GROUP_CONCAT(DISTINCT t1.remarks) AS remarks,GROUP_CONCAT(DISTINCT t1.doctor) AS doctor" +
                " FROM " + dtable+ " t1 LEFT JOIN " + ptable + " t2 ON t1.pay_service_id=t2.id" +
                " WHERE t1.reservations_id='" + reservationsId + "' AND (t1.deal_status='已开单' or t1.deal_status='待审核') " +
                " group by t1.get_time order by t1.get_time desc ";
        List<PayServicedInfo> NoPayServiceInfoList = sqlMapper.selectList(sqlno, PayServicedInfo.class);
        return NoPayServiceInfoList;
    }

    public int addCustomerErnest(String remarks, String customerId, String[] str, String mode, String reservationsId, String fieldTcValue,
                                 String abbreviation, String areaAbbreviation, String outpatientName,String userName,String sttleAccountsTime,String memberSwitch ) {
        String ctable = abbreviation + "customer_info";
        String rtable = abbreviation + "recharge_info";
        String vtable=areaAbbreviation+"vip_level_info";
        Double money = 0.0;
        String checkoutMethod = "";
        String sql1 = "select * from " + ctable + " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        if ("转账".equals(mode)) {
            String sql2 = "select * from " + ctable + " where arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null " +
                    " and reservations_id='" + reservationsId + "' limit 0,1 ";
            CustomerInfo customerInfor2 = sqlMapper.selectOne(sql2, CustomerInfo.class);
            String customerIdr = customerInfor2.getId();
            for (int i = 0; i < str.length; i++) {//收费方式
                checkoutMethod = str[i].split("#")[0];
                money = Double.parseDouble(str[i].split("#")[1]);
                Random rand = new Random();
                int randomNumber = rand.nextInt(999999);
                String id = randomNumber + DateTimeKit.now();
                //扣除本人的储值 //插入结账表
                String sql2c = " INSERT INTO `" + rtable + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                        " ('" + id + "',1,'" + customerId + "','" + customerInfo.getReservationsId() + "', " +
                        " '" + userName +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '-" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '储值转账" + remarks + "','是','" + fieldTcValue + "',2, '-" + money + "',0) ";
                sqlMapper.insert(sql2c);
                //插入储值收费记录表
                String sql2c1 = " INSERT INTO reserveMoney_deal (`status`, `reservations_id`, `customer_id`,`reservations_ids`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                        " (1,'" + customerInfo.getReservationsId() + "', '" + customerId + "', '" + reservationsId + "'," +
                        " '" + userName +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '-" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remarks + "','是','" + fieldTcValue + "','" + outpatientName + "'" +
                        ",'" + mode + "','" + id + "') ";
                sqlMapper.insert(sql2c1);
                //转入收款人的账户
                String sql22 = " INSERT INTO reserveMoney_deal ( `status`,`reservations_id`,`customer_id`, `reservations_idz`," +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                        " (1,'" + reservationsId + "','" + customerInfor2.getId() + "','" + customerInfo.getReservationsId() + "'," +
                        " '" + userName +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remarks + "','是','" + fieldTcValue + "','" + outpatientName + "'" +
                        ",'收账','zz" + id + "') ";
                sqlMapper.insert(sql22);
                String sql22r = " INSERT INTO " + rtable + " (`id`, `status`, `customer_id`, `reservations_id`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                        " ('zz" + id + "',1,'" + customerIdr + "','" + reservationsId + "', " +
                        " '" + userName +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , '" + money + "', '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '储值转账" + remarks + "','是','" + fieldTcValue + "',2, '" + money + "',0) ";
                sqlMapper.insert(sql22r);
                String sql11 = "update " + ctable + " a set money_balance=money_balance+'" + money + "',update_people='" + userName + "'," +
                        "update_time='" + DateTimeKit.now() + "' where reservations_id='" + reservationsId + "'";
                sqlMapper.update(sql11);
                String sql = "update " + ctable + " a set money_balance=money_balance+'-" + money + "',update_people='" + userName + "'," +
                        "update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
                String sqlright = "update " + rtable + " a set a.recharges_time=left( DATE_ADD(a.recharge_time,INTERVAL 1 DAY) ,10) " +
                        " where right(a.recharge_time,8)>= '" + sttleAccountsTime + "' and a.recharges_time is null;";
                sqlMapper.update(sqlright);
                String qlleft = "update " + rtable + " a set a.recharges_time=left(a.recharge_time,10) " +
                        " where right(a.recharge_time,8)<'" + sttleAccountsTime + "' and a.recharges_time is null;";
                sqlMapper.update(qlleft);
                sqlMapper.update(sql);
            }
        } else {
            String remark = "";
            if ("退款".equals(mode)) {
                remark = "储值退款";
            } else if ("充值".equals(mode)) {
                remark = "储值充值";
            }
            Double moneysum = 0.0;
            for (int i = 0; i < str.length; i++) {//收费方式
                checkoutMethod = str[i].split("#")[0];
                money = Double.parseDouble(str[i].split("#")[1]);
                Random rand = new Random();
                int randomNumber = rand.nextInt(999999);
                String id = randomNumber + DateTimeKit.now();
                moneysum += money;
                String sql2 = " INSERT INTO `" + rtable + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                        " ('" + id + "',1,'" + customerId + "','" + customerInfo.getReservationsId() + "', " +
                        " '" + userName +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remark + remarks + "','是','" + fieldTcValue + "',2 , " + money + ",0) ";
                sqlMapper.insert(sql2);
                String sql2r = " INSERT INTO reserveMoney_deal (`status`, `reservations_id`, `customer_id`," +
                        "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                        "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                        "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                        " (1,'" + customerInfo.getReservationsId() + "', '" + customerId + "', " +
                        " '" + userName +
                        "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                        + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                        " '" + remarks + "','是','" + fieldTcValue + "','" + outpatientName + "'" +
                        ",'" + mode + "','" + id + "') ";
                sqlMapper.insert(sql2r);
                String sql = "update " + ctable + " a set money_balance=money_balance+'" + money + "',update_people='" + userName + "'," +
                        "update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
                sqlMapper.update(sql);
                String sqlright = "update " + rtable + " a set a.recharges_time=left( DATE_ADD(a.recharge_time,INTERVAL 1 DAY) ,10) " +
                        " where right(a.recharge_time,8)>= '" + sttleAccountsTime + "' and a.recharges_time is null;";
                sqlMapper.update(sqlright);
                String qlleft = "update " + rtable + " a set a.recharges_time=left(a.recharge_time,10) " +
                        " where right(a.recharge_time,8)<'" + sttleAccountsTime + "' and a.recharges_time is null;";
                sqlMapper.update(qlleft);
            }
            if ("1".equals(memberSwitch)) {
                //返现
                if ("退款".equals(mode)) {
                    moneysum = -moneysum;
                }
                String sqlb = "select * from "+ vtable +" ";
                List<VipLevelInfo> infod2 = sqlMapper.selectList(sqlb, VipLevelInfo.class);
                Double allReturnCash = 0.0;
                for (VipLevelInfo vinfo : infod2) {
                    if (vinfo.getMoneyCoefficientTwo() > moneysum && moneysum >= vinfo.getMoneyCoefficient()) {
                        allReturnCash = vinfo.getAddMoney();
                    }
                }
                if ("退款".equals(mode)) {
                    allReturnCash = -allReturnCash;
                }
                String insert2 = "insert into return_cash_info (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_time,add_store) values" +
                        " ('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "'," +
                        " '" + allReturnCash + "','返现','返现" + remarks + "'," +
                        " '" + userName + "','" + DateTimeKit.now() + "','" + outpatientName + "')";
                sqlMapper.insert(insert2);
            }
        }
        return 1;
    }

    public List<UserDto> getReferee(String name,String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql = "select  t.customer_name as account,t.reservations_id as reservationsId from " + ctable + " t where t.customer_name like '%" + name + "%'" +
                " or t.customer_phone like '%" + name + "%' or t.szm like '%" + name + "%' group by t.reservations_id ";
        List<UserDto> list = sqlMapper.selectList(sql, UserDto.class);
        return list;
    }

    public int addCustomerScore(String jfbalance, String remarks, String mode, String customerId, String reservationsId,
                                String abbreviation, String outpatientName,String userName,String proportional){
        String ctable = abbreviation + "customer_info";
        String sql1 = "select * from " + ctable + " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        String id = randomNumber + DateTimeKit.now();
        BigDecimal b1 = new BigDecimal(Double.parseDouble(proportional));
        BigDecimal b2 = new BigDecimal(Double.parseDouble(jfbalance));
        Double jfbalances = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        if ("转积分".equals(mode)) {
            String sqla1 = "select * from " + ctable + " where reservations_id='" + reservationsId + "' limit 1";
            CustomerInfo cinfo = sqlMapper.selectOne(sqla1, CustomerInfo.class);
            if (cinfo == null) {   //转入人没来过本店，去过另一家店
                String sqla2 = "select * from distinction_customer_status a where reservations_id='" + reservationsId + "' and status=1  limit 1";
                Dept infod = sqlMapper.selectOne(sqla2, Dept.class);
                String sqld1 = "select * from " + infod.getAbbreviation() + "customer_info" + " " +
                        "where reservations_id ='" + reservationsId + "' order by arrival_time desc limit 1 ";
                cinfo = sqlMapper.selectOne(sqld1, CustomerInfo.class);
                ctable = infod.getAbbreviation() + "customer_info";
            }
            String insert1 = "insert into score_deal (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_store) values" +
                    " ('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "'," +
                    " '-" + jfbalance + "','" + mode + "','积分转出" + remarks + "'," +
                    " '" + userName + "','" + outpatientName + "')";
            sqlMapper.insert(insert1);
            String insert2 = "insert into score_deal (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_store) values" +
                    " ('" + cinfo.getReservationsId() + "','" + cinfo.getCustomerName() + "','" + cinfo.getCustomerPhone() + "'," +
                    " '" + jfbalance + "','" + mode + "','积分转入" + remarks + "'," +
                    " '" + userName + "','" + outpatientName + "')";
            sqlMapper.insert(insert2);
            String sql = "update " + ctable + " a set jfbalance=jfbalance-'" + jfbalance + "',update_people='" + userName + "'," +
                    "update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
            String sql2 = "update " + ctable + " a set jfbalance=jfbalance+'" + jfbalance + "',update_people='" + userName + "'," +
                    "update_time='" + DateTimeKit.now() + "' where reservations_id='" + cinfo.getReservationsId() + "'";
            sqlMapper.update(sql2);
            return sqlMapper.update(sql);
        } else {
            String sqlinsert = "insert into score_deal (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_store) values" +
                    " ('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "','" + jfbalance + "','" + mode + "','" + remarks + "'," +
                    " '" + userName + "','" + outpatientName + "')";
            sqlMapper.insert(sqlinsert);
            String sql = "update " + ctable + " a set jfbalance=jfbalance+'" + jfbalance + "',update_people='" + userName + "'," +
                    "update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
            return sqlMapper.update(sql);
        }
    }

    public int verificationCodeTwo(String customerId, String productId,String abbreviation,String outpatientName,String userName){
        String ctable = abbreviation + "customer_info";
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        String sql1 = "select * from " + ctable + " where id='" + customerId + "' limit 1";
        CustomerInfo cinfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String strSql = "insert into point_conversion_info (product_id,reservations_id,code) " +
                "values ('" + productId + "','" + cinfo.getReservationsId() + "','" + randomNumber + "');";
        String upSql = "update point_reword_info a set a.conversioned_number=conversioned_number+1," +
                "a.putaway_number=putaway_number-1 where id='" + productId + "';"; //减少商品上架的数量，减少会员的储值余额
        // 插入到积分表中
        String insertSql = "insert into score_deal (reservations_id,customer_name,customer_phone,score_balance,mode,add_people,add_store) " +
                "values('" + cinfo.getReservationsId() + "','" + cinfo.getCustomerName() + "','" + cinfo.getCustomerPhone() + "'," +
                "-(select product_price from point_reword_info where id='" + productId + "'),'商品兑换'," +
                "'" + userName + "','" + outpatientName + "')";
        sqlMapper.insert(insertSql);
        sqlMapper.insert(strSql);
        return sqlMapper.update(upSql);
    }

    public int updateReturnCashInfo(String jfbalance, String remarks, String mode, String customerId, String reservationsId,
                                    String abbreviation,String outpatientName,String userName){
        String ctable = abbreviation + "customer_info";
        String sql1 = "select * from " + ctable+ " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        if ("返现转账".equals(mode)) {
            String sqla1 = "select * from " + ctable+ " where reservations_id='" + reservationsId + "' limit 1";
            CustomerInfo cinfo = sqlMapper.selectOne(sqla1, CustomerInfo.class);
            if (cinfo == null) {   //转入人没来过本店，去过另一家店
                String sqla2 = "select * from distinction_customer_status a where reservations_id='" + reservationsId + "' and status=1  limit 1";
                Dept infod = sqlMapper.selectOne(sqla2, Dept.class);
                String sqld1 = "select * from " + infod.getAbbreviation() + "customer_info" + " " +
                        "where reservations_id ='" + reservationsId + "' order by arrival_time desc limit 1 ";
                cinfo = sqlMapper.selectOne(sqld1, CustomerInfo.class);
            }
            String insert1 = "insert into return_cash_info (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_time,add_store) values" +
                    " ('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "'," +
                    " '-" + jfbalance + "','" + mode + "','返现转出" + remarks + "'," +
                    " '" + userName + "','" + DateTimeKit.now() + "','" + outpatientName + "')";
            sqlMapper.insert(insert1);
            String insert2 = "insert into return_cash_info (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_time,add_store) values" +
                    " ('" + cinfo.getReservationsId() + "','" + cinfo.getCustomerName() + "','" + cinfo.getCustomerPhone() + "'," +
                    " '" + jfbalance + "','" + mode + "','返现转入" + remarks + "'," +
                    " '" + userName + "','" + DateTimeKit.now() + "','" + outpatientName + "')";
            sqlMapper.insert(insert2);
        } else {
            String insert1 = "insert into return_cash_info (reservations_id,customer_name,customer_phone,score_balance,mode,remarks,add_people,add_time,add_store) values" +
                    " ('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "'," +
                    " '" + jfbalance + "','" + mode + "','" + remarks + "'," +
                    " '" + userName + "','" + DateTimeKit.now() + "','" + outpatientName + "')";
            sqlMapper.insert(insert1);
        }
        return 1;
    }

    public int addOrthodonticsDeal(String remarks, String customerId, String[] str, String mode, String fieldTcValue,
                                   String abbreviation, String outpatientName,String userName,String sttleAccountsTime) {
        String ctable = abbreviation + "customer_info";
        String rtable = abbreviation + "recharge_info";
        Double money = 0.0;
        String checkoutMethod = "";
        String sql1 = "select * from " + ctable + " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql="select ifnull(sum(actual_account_balance),0) as moneyBalance from reservemoney_orthodontics_deal t1" +
                " where reservations_id='"+customerInfo.getReservationsId()+"' and (now() between t1.recharge_time and t1.end_time) ";
        CustomerInfo cu=sqlMapper.selectOne(sql,CustomerInfo.class);
        String remark = "";
        if ("退款".equals(mode)) {
            remark = "种植牙俱乐部退回费";
        } else if ("充值".equals(mode)) {
            if(cu.getMoneyBalance()==1000){
                return 2;
            }
            String sql2 = "update " + ctable + "" +
                    " set deal_status='已结账',isdeal_status='已成交'" +
                    " where id='" + customerId + "' ";
            sqlMapper.update(sql2);
            String sqldist = "select a.id from distinction_customer_status a where a.reservations_id='" + customerInfo.getReservationsId() + "' and deal_status='未成交'";
            List<PayServicedInfo> sqldistl = sqlMapper.selectList(sqldist, PayServicedInfo.class);
            if(sqldistl.size()>0){  //之前未成交，本次成交了，修改时间状态
                String up1 = "update distinction_customer_status set deal_status='已成交',update_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "' " +
                        " and status=1  and arrival_store='" + outpatientName + "';";
                sqlMapper.update(up1);
            }
            remark = "种植牙俱乐部入会充值";
        }
        Double moneysum = 0.0;
        for (int i = 0; i < str.length; i++) {//收费方式
            checkoutMethod = str[i].split("#")[0];
            money = Double.parseDouble(str[i].split("#")[1]);
            Random rand = new Random();
            int randomNumber = rand.nextInt(999999);
            String id = randomNumber + DateTimeKit.now();
            moneysum += money;
            String sql2 = " INSERT INTO `" + rtable + "` (`id`, `status`, `customer_id`, `reservations_id`, " +
                    "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                    "  `actual_account_balance`, `recharge_time`, `operation_time`, " +
                    "  `remarks`, `is_record`,tc_name,isEarnes,hospital_performance,section_performance) values  " +
                    " ('" + id + "',1,'" + customerId + "','" + customerInfo.getReservationsId() + "', " +
                    " '" + userName +
                    "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                    + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "', " +
                    " '" + remark + remarks + "','是','" + fieldTcValue + "',10 , " + money + ",0) ";
            sqlMapper.insert(sql2);
            String sql2r = " INSERT INTO reservemoney_orthodontics_deal (`status`, `reservations_id`, `customer_id`," +
                    "  `recharge_people`,`checkout_method_id`, `checkout_method`, " +
                    "  `actual_account_balance`, `recharge_time`, `operation_time`,end_time, " +
                    "  `remarks`, `is_record`,tc_name,add_store,mode,deal_id) values  " +
                    " (1,'" + customerInfo.getReservationsId() + "', '" + customerId + "', " +
                    " '" + userName +
                    "',(select id from checkout_method_info where checkout_method='" + checkoutMethod + "'),'"
                    + checkoutMethod + "' , " + money + ", '" + DateTimeKit.now() + "', '" + DateTimeKit.now() + "',DATE_ADD('" + DateTimeKit.now() + "', INTERVAL 1 YEAR),  " +
                    " '" + remarks + "','是','" + fieldTcValue + "','" + outpatientName + "'" +
                    ",'" + mode + "','" + id + "') ";
            sqlMapper.insert(sql2r);
            String sqlright = "update " + rtable + " a set a.recharges_time=left( DATE_ADD(a.recharge_time,INTERVAL 1 DAY) ,10) " +
                    " where right(a.recharge_time,8)>= '" + sttleAccountsTime+ "' and a.recharges_time is null;";
            sqlMapper.update(sqlright);
            String qlleft = "update " + rtable + " a set a.recharges_time=left(a.recharge_time,10) " +
                    " where right(a.recharge_time,8)<'" + sttleAccountsTime+ "' and a.recharges_time is null;";
            sqlMapper.update(qlleft);
        }
        return 1;
    }

    public List<ScoreDealInfo> getScoreDealList(String reservationsId){
        String sql = "select id,customer_name as customerName,customer_phone as customerPhone,score_balance as scoreBalance,mode," +
                "remarks,add_people as addPeople,add_store as addStore,add_time as addTime from score_deal where reservations_id='" + reservationsId + "' order by add_time desc";
        List<ScoreDealInfo> scoreDealInfo = sqlMapper.selectList(sql, ScoreDealInfo.class);
        return scoreDealInfo;
    }

    public List<ScoreDealInfo> getreturnCashRecordList(String reservationsId){
        String sql = "select id,customer_name as customerName,customer_phone as customerPhone,score_balance as scoreBalance,mode," +
                "remarks,add_people as addPeople,add_store as addStore,add_time as addTime from return_cash_info" +
                " where reservations_id='" + reservationsId + "' order by add_time desc";
        List<ScoreDealInfo> scoreDealInfo = sqlMapper.selectList(sql, ScoreDealInfo.class);
        return scoreDealInfo;
    }

    public int isMarket(String oneChannel){
        String sq1 = "select ismarket from channel where channelName='" + oneChannel + "' and pid=0 limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sq1, CustomerInfo.class);
        return customerInfo.getIsmarket();
    }

    public CustomerInfo isVoid(String customerId,String abbreviation){
        String dtable = abbreviation + "deal_info";
        String rtable = abbreviation + "recharge_info";
        CustomerInfo customerInfo = new CustomerInfo();
        String sq1 = "select id from " + rtable + " where customer_id='" + customerId + "' and (isEarnes=2 or isEarnes=3) limit 1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sq1, RechargeInfo.class);
        if (rechargeInfo != null) {  //充过积分或储值
            customerInfo.setMoneyBalance(11.11);
        }
        String sql2 = "select id from " + dtable + " where customer_id='" + customerId + "' limit 1";
        RechargeInfo rechargeInfo1 = sqlMapper.selectOne(sql2, RechargeInfo.class);
        if (rechargeInfo1 != null) { //存在结账表中
            customerInfo.setDealStatus("已开单");
        }
        return customerInfo;
    }

    public int updateGetTime(String customerId, String updateTime,String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + " t set t.arrival_time = '" + updateTime + "' where t.id = '" + customerId + "' ";
        return sqlMapper.update(sql);
    }


    public int updateChildrenCardOk(String reservationsId, String childrenCard, String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + " set children_card ='" + childrenCard
                + "' where reservations_id ='" + reservationsId + "' ;";
        return sqlMapper.update(sql);
    }

    public CustomerInfo selectOneById(String id,String abbreviation) {
        String ctable = abbreviation + "customer_info";
        String sql = "select " +
                " (CASE t2.customer_sex  WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END ) as sex ," +
                " (CASE t2.marital_status WHEN 1 THEN '已婚' WHEN 2 THEN '未婚' WHEN 3 THEN '离异' ELSE '未知' END ) as maritalStatusName ," +
                "t2.* from " + ctable + " t2 where id= '" + id + "';";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public int update_CustomeInfoValue(CustomerInfo info,String customerId,String abbreviation,String userId,String userName,String outpatientName ){
        String ctable = abbreviation + "customer_info";
        String mtable = abbreviation + "medicalrecord_info";
        String sql11 = "select count(1) as amount from " + ctable + " where upper(medical_record_number)=upper('" + info.getMedicalRecordNumber() + "') " +
                " and reservations_id !='" + info.getReservationsId() + "' ";
        CustomerInfo customer = sqlMapper.selectOne(sql11, CustomerInfo.class);
        CustomerInfo info1 = this.selectOneById(info.getCustomerId(),info.getAbbreviation());
        if (customer.getAmount() > 0) {
            return 0;
        } else {
            String sql = "update " + ctable + " set customer_name ='" + info.getCustomerName() + "'" +
                    ",birthday='" + info.getBirthday() + "'" +
                    ",age_range= " +
                    "(case " +
                    " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<10) then '0-9' " +
                    " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<20) then '10-19' " +
                    " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<30) then '20-29' " +
                    " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<40) then '30-39' " +
                    " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<50) then '40-49' " +
                    " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<60) then '50-59' " +
                    " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<70) then '60-69' " +
                    " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<80) then '70-79' " +
                    " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<90) then '80-89' " +
                    " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<100) then '90-99' " +
                    " else '99+' end)" +
                    ",customer_age=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS('" + info.getBirthday() + "')),'%Y')),customer_sex='" + info.getCustomerSex() + "'" +
                    ",medical_record_number='" + info.getMedicalRecordNumber() + "',referee='" + info.getReferee() + "',referee_ship='" + info.getRefereeShip() + "'" +
                    ",province='" + info.getProvince() + "',cities='" + info.getCities() + "',region='" + info.getRegion() + "',address='" + info.getAddress() + "'" +
                    ",occupation='" + info.getOccupation() + "',consum_ability='" + info.getConsumAbility() + "',consumption_habits='" + info.getConsumptionHabits() +
                    "',id_card_no='" + info.getIdCardNo() + "',fixed_telephone='" + info.getFixedTelephone() + "',qq_number='" + info.getQqNumber() +
                    "',wechat_Number='" + info.getWechatNumber() + "',email='" + info.getEmail() + "',zip_code='" + info.getZipCode() +
                    "',marital_status='" + info.getMaritalStatus() + "',is_have_children='" + info.getIsHaveChildren() + "',characters='" + info.getCharacters() +
                    "',remarks='" + info.getRemarks() + "',customer_sex_head='" + customerPublicService.getSexPt(info) + "' " +
                    " where reservations_id ='" + info.getReservationsId() + "';";
            String upsql = "update " + ctable + " set " +
                    "update_people='" + userName + "',update_time='" + DateTimeKit.now() + "' where id ='" + info.getId() + "';";

            if (!info.getMedicalRecordNumber().equals(info1.getMedicalRecordNumber())) {
                //插入号修改日志
                String sq11 = "INSERT INTO `customer_medical_number_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                        " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                        " ('" + DateTimeKit.now() + "','" + userName + "','修改','" +userId + "','" + info1.getCustomerStatus() + "', '"
                        + info1.getCustomerName() + "', '" + info1.getCustomerPhone() + "','成功','" + outpatientName +
                        "','" + userName + " :修改了 " + info1.getCustomerName() + " 的病历号 ','" + info1.getId() + "','" + info1.getReservationsId() + "');";
                sqlMapper.insert(sq11);
                String sqlupodamesql1 = "update " + mtable + " a set customer_phone=null where customer_phone='" + info1.getCustomerPhone() + "'";
                sqlMapper.update(sqlupodamesql1);
                String sqlupodamesql2 = "update " + mtable + " a set customer_phone='" + info1.getCustomerPhone() + "' where CONCAT(a.prefix_part_one,a.prefix_part_two,a.num)='" + info.getMedicalRecordNumber() + "'";
                sqlMapper.update(sqlupodamesql2);
                String updsql1 = "update distinction_customer_status set medical_record_number='" + info.getMedicalRecordNumber() + "' where reservations_id='" + info1.getReservationsId() + "' and status=1 ";
                sqlMapper.update(updsql1);
                String updsql2 = "update reservations_info set identification_number='" + info.getMedicalRecordNumber() + "' where id='" + info1.getReservationsId() + "'";
                sqlMapper.update(updsql2);
                String updsql3 = "update reservations_infosc set identification_number='" + info.getMedicalRecordNumber() + "' where id='" + info1.getReservationsId() + "'";
                sqlMapper.update(updsql3);
            }
            sqlMapper.update(upsql);
            return sqlMapper.update(sql);
        }
    }

    public int batchChoiceTc(String fieldTc, String tcName, String remarks,String abbreviation,String userName){
        String ctable = abbreviation + "customer_info";
        String rek = "";
        if (StringUtil.isNotEmpty(remarks)) {
            rek = "；" + remarks + "";
        }
        String sql = "update " + ctable + " a set query_tc='" + tcName + "' ,remarks=concat(remarks,'" + rek + "') where field_tc='" + fieldTc + "'";
        sqlMapper.update(sql);
        String insql = "INSERT INTO `batch_choice_tc_log` (`updater`, `originaltc`, `nowtc`) VALUES " +
                "('" + userName + "','" + fieldTc + "', '" + tcName + "');";
        return sqlMapper.insert(insql);
    }

    public int update_mediumValue(String oneMedium2, String twoMedium2, String thrMedium2, String customerId, String abbreviation,String userName){
        String ctable = abbreviation + "customer_info";
        String sql2 = "select " +
                " ( " +
                " CASE a.customer_sex " +
                "  WHEN 1 THEN " +
                "   '男' " +
                "  WHEN 2 THEN " +
                "   '女' " +
                "  ELSE " +
                "   '未知' " +
                "  END " +
                "  ) as sex," +
                "a.* from " + ctable + " a where id='" + customerId + "'";
        CustomerInfo info = sqlMapper.selectOne(sql2, CustomerInfo.class);
        String sql = "update " + ctable + " set one_medium ='" + oneMedium2 + "',two_medium='" + twoMedium2 + "'" +
                ",thr_medium='" + thrMedium2 + "',update_people='" + userName + "',update_time='" + DateTimeKit.now()
                + "' where reservations_id ='" +  info.getReservationsId()  + "' ;";
        String sqls = "update reservations_info set one_medium ='" + oneMedium2 + "',two_medium='" + twoMedium2 + "'" +
                ",thr_medium='" + thrMedium2 + "',update_people='" + userName + "',update_time='" + DateTimeKit.now()
                + "' where id ='" + info.getReservationsId() + "' ;";
        sqlMapper.update(sqls);
        String sq13 = "INSERT INTO `medium_log_info` ( `customer_name`,`customer_phone`,`person`, `createtime`,`one_medium1`,`two_medium1`,`thr_medium1`," +
                "one_medium2,two_medium2,thr_medium2) VALUES " +
                " ( '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + userName + "','" + DateTimeKit.now() + "'," +
                "'" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() +
                "', '" + oneMedium2 + "', '" + twoMedium2 + "','" + thrMedium2 + "');";
        sqlMapper.insert(sq13);//插入媒介修改日志
        return sqlMapper.update(sql);
    }

    public int choiceDoctor(String customerId, String doctor, String remarks, String abbreviation){
        String ctable = abbreviation + "customer_info";
        String rek = "";
        if (StringUtil.isNotEmpty(remarks)) {
            rek = "；" + remarks + "";
        }
        String sq1 = "update " + ctable + " a set a.doctor='" + doctor + "',remarks=concat(remarks,'" + rek + "') where id='" + customerId + "'";
        return sqlMapper.update(sq1);
    }

    public int choiceTc(String customerId, String tcName, String remarks,String abbreviation,String userId,String userName,String outpatientName){
        String ctable = abbreviation + "customer_info";
        String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id,a.field_tc,a.query_tc from " + ctable + " a where id = '" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String rek = "";
        if (StringUtil.isNotEmpty(remarks)) {
            rek = "；" + remarks + "";
        }
        String sq1 = "update " + ctable + " a set a.field_tc='" + tcName + "',a.query_tc='" + tcName + "',remarks=concat(remarks,'" + rek + "') where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName+ "','修改TC','" +userId+ "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                "','现场tc（归属tc）：" + customerInfo.getFieldTc() + "(" + customerInfo.getQueryTc() + ")->" + tcName + "','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sq1);
    }

    public int treatmentUnitGrade(String initialization, String customerId,String treatmentUnitY, String areaAbbreviation){
        String ptable = areaAbbreviation + "pay_service_grade_info";
        String sqlpy = "select ((select a.grade from " + ptable + " a where a.department='" + initialization + "' limit 1)-" +
                "(select a.grade from " +ptable+ " a where a.department='" + treatmentUnitY + "' limit 1)) as grade from dual";
        PayServicedInfo py = sqlMapper.selectOne(sqlpy, PayServicedInfo.class);
        if (Integer.parseInt(py.getGrade()) > 0) {  //修改的科室等级大于原科室的等级需要审核
            return 1;
        } else {
            return 2;   //修改的科室等级小于原科室的等级 直接修改
        }
    }

    public int update_TreatmentUnitValueOne(String initialization, String customerId, String remarks, String treatmentUnitY,
                                            String abbreviation,Integer auditOfDept,String userName,String outpatientName){
        String ctable = abbreviation + "customer_info";
        String sql1 = "select * from " + ctable + " where id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        if (auditOfDept == 1) {  //修改的科室等级大于原科室的等级需要审核
            String sql = "INSERT INTO  treatment_unitl_log (reservations_id,medical_record_number,customer_id,`customer_name`,`customer_phone`,`ytreatment_unitl`,`xtreatment_unitl`," +
                    " `remarks`,outpatient_name,add_time,status,add_people) VALUES" +
                    "('" + customerInfo.getReservationsId() + "','" + customerInfo.getMedicalRecordNumber() + "','" + customerInfo.getId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "','"
                    + customerInfo.getTreatmentUnit() + "','" + initialization + "','" + remarks + "','" + outpatientName + "','"
                    + DateTimeKit.now() + "','待审核','" +userName+ "');";
            return sqlMapper.insert(sql);
        } else {  //修改的科室等级大于原科室的等级不需要审核
            String sql = "INSERT INTO  treatment_unitl_log (reservations_id,medical_record_number,customer_id,`customer_name`,`customer_phone`,`ytreatment_unitl`,`xtreatment_unitl`," +
                    " `remarks`,outpatient_name,add_time,status,add_people) VALUES" +
                    "('" + customerInfo.getReservationsId() + "','" + customerInfo.getMedicalRecordNumber() + "','" + customerInfo.getId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "','"
                    + customerInfo.getTreatmentUnit() + "','" + initialization + "','" + remarks + "','" + outpatientName + "','"
                    + DateTimeKit.now() + "','待审核','" +userName + "');";
            String upsql = "update " + ctable + " set treatment_unit='" + initialization + "' where id='" + customerId + "'";
            sqlMapper.update(upsql);
            return sqlMapper.insert(sql);
        }
    }

    public int update_TreatmentUnitValueTwo(String initialization, String customerId,String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + " set treatment_unit='" + initialization + "' where id='" + customerId + "'";
        return sqlMapper.update(sql);//修改的科室等级小于原科室的等级 直接修改
    }

    public int setZphone(String customerPhone, String customerPhone2, String customerId ,String abbreviation,String userName,String outpatientName){
        String ctable = abbreviation + "customer_info";
        String sql1 = "select reservations_id from " + ctable + " a where id='" + customerId + "' ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql = "update " + ctable + " set customer_phone ='" + customerPhone + "',customer_phone2='" + customerPhone2 + "'" +
                ",update_people='" + userName+ "',update_time='" + DateTimeKit.now()
                + "' where reservations_id ='" + customerInfo.getReservationsId() + "' ;";
        String sql3 = "update members_user set account ='" + customerPhone + "' ,phone='" + customerPhone +
                "' where reservations_id ='" + customerInfo.getReservationsId() + "' ;";
        sqlMapper.update(sql3);
        return sqlMapper.update(sql);
    }

    public int addPhone(String customerPhone3, String customerId,String abbreviation,String userName,String outpatientName){
        String ctable = abbreviation + "customer_info";
        String sql1 = "select * from " + ctable + "  where id ='" + customerId + "' ;";
        CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql = "select id from reservations_info where (customer_phone ='" + customerPhone3 + "' or customer_phone2='" + customerPhone3 + "') and " +
                " id!='" + info.getReservationsId() + "' limit 1;";
        CustomerInfo infos = sqlMapper.selectOne(sql, CustomerInfo.class);
        if (infos != null) {//查询已有手机号的预约集合
            List<ReservationsInfo> existList = this.getCustomerPhoneCounts(customerPhone3);
            int count = existList.size();
            customerPhone3 = customerPhone3 + "+" + count;
        }
        if (info.getCustomerPhone2() != null) {
            String sql3 = "update " + ctable + " set customer_phone2=concat(customer_phone2,'," + customerPhone3 + "')," +
                    "update_people='" + userName+ "',update_time='" + DateTimeKit.now()
                    + "' where reservations_id ='" + info.getReservationsId() + "' ;";
            String sql32 = "update reservations_info set customer_phone2=concat(customer_phone2,'," + customerPhone3 + "')," +
                    "update_people='" + userName+ "',update_time='" + DateTimeKit.now()
                    + "' where id ='" + info.getReservationsId() + "' ;";
            sqlMapper.update(sql32);
            return sqlMapper.update(sql3);
        } else {
            String sql3 = "update " + ctable + " set customer_phone2='" + customerPhone3 + "'," +
                    "update_people='" + userName+ "',update_time='" + DateTimeKit.now()
                    + "' where reservations_id ='" + info.getReservationsId() + "' ;";
            String sql33 = "update reservations_info set customer_phone2='" + customerPhone3 + "'," +
                    "update_people='" + userName+ "',update_time='" + DateTimeKit.now()
                    + "' where id ='" + info.getReservationsId() + "' ;";
            sqlMapper.update(sql33);
            return sqlMapper.update(sql3);
        }
    }

    public List<ReservationsInfo> getCustomerPhoneCounts(String customerPhone) {
        String sql = "select id from reservations_info where (customer_phone like '%" + customerPhone + "%' or customer_phone2 like '%" + customerPhone + "%' )" +
                " order by filing_time desc ;";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList;
    }

    public int toVoid(String toVoidremarks, String customerId,String abbreviation,String userName,String outpatientName){
        String ctable = abbreviation + "customer_info";
        String sqlcu = "select reservations_id from " + ctable + " where id = '" + customerId+ "' limit 1";
        CustomerInfo customerInfo1 = sqlMapper.selectOne(sqlcu, CustomerInfo.class);
        String sqlse = "select id from reservations_infosc t where id='" + customerInfo1.getReservationsId() + "' " +
                " and left(t.first_visit_date,10)=left(now(),10) and t.arrival_store='" + outpatientName + "'";
        CustomerInfo customerInfo2 = sqlMapper.selectOne(sqlse, CustomerInfo.class);
        if (customerInfo2 != null) { //匹配市场初到，将市场到店状态改回预约
            String sqlsc = "update reservations_infosc set status='预约' , first_visit_date=null ,arrival_store=null where id = '" + customerInfo2.getId() + "'";
            sqlMapper.update(sqlsc);
            String sql = "update reservations_info set status='预约' , first_visit_date=null ,arrival_store=null  where id = '" + customerInfo2.getId() + "'";
            sqlMapper.update(sql);
        } else {
            String sqlz = "select id from reservations_info t where id='" + customerInfo1.getReservationsId() + "' " +
                    " and left(t.first_visit_date,10)=left(now(),10) and t.arrival_store='" + outpatientName + "'";
            CustomerInfo customerInfoz = sqlMapper.selectOne(sqlz, CustomerInfo.class);
            if (customerInfoz != null) {
                String deletesql = "delete from reservations_info where id='" + customerInfo1.getReservationsId() + "'";
                sqlMapper.delete(deletesql);
            }
        }
        String sql = "update " + ctable + " set customer_status='作废' , to_void_remarks='" + toVoidremarks + "'" +
                ", to_void_people='" + userName+ "',to_void_time='" + DateTimeKit.now() + "',arrival_status='8' " +
                " where id = '" + customerId + "'";
        return sqlMapper.update(sql);
    }

    public int updAppointmentPayServiceOK(String customerId,String projectData,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id ,a.appointment_pay_service from " + ctable + " a where id = '" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = "update " + ctable + " set appointment_pay_service='" + projectData + "'" +
                " where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName + "','修改看诊项目','" +userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName +
                "','修改看诊项目：" + customerInfo.getAppointmentPayService() + "->" + projectData + "','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.update(sql);
        return sqlMapper.insert(sq11);
    }

    public int updateRemarksReservationOk(String customerId, String remarksReservation,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id ,a.remarks_reservation from " + ctable + " a where id = '" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = "update " + ctable + " set remarks_reservation='" + remarksReservation + "'" +
                " where id='" + customerId + "'";
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','修改预约备注','" + userId+ "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','修改预约备注：" + customerInfo.getRemarksReservation() + "->" + remarksReservation + "','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.update(sql);
        return sqlMapper.insert(sq11);
    }

    public int updateNumber(String customerId, String yellowNumber,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String sql1 = "select t.customer_name as customerName,t.customer_phone as customerPhone,t.yellow_number as yellowNumber,t.archiving as archiving,t.id," +
                "t.reservations_id as reservationsId from " + ctable + " t where id='" + customerId + "' limit 1";
        CustomerInfo info = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql = "update " + ctable + "  set yellow_number ='" + yellowNumber + "'" +
                " where reservations_id = '" + info.getReservationsId() + "'";

        String sq11 = "INSERT INTO yellow_number_update_log (logtype, userid,customer_name,customer_phone,single_person, createtime,succeed,classname) VALUES " +
                " ( '修改'," + userId + ", '" + info.getCustomerName() + "', '" + info.getCustomerPhone() + "', '" + userName+ "','" + DateTimeKit.now() + "'," +
                "'成功','" + userName + " 把 " + info.getCustomerName() + " 的黄页编号:" + info.getYellowNumber() + " 修改为:" + yellowNumber + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public List<CustomerReservationsUpdateLogInfo> getLogList(Page<CustomerReservationsUpdateLogInfo> page, SearchCustomerReservationsUpdateLogInfo searchInfo) {
        String ctable = searchInfo.getAbbreviation() + "customer_info";
        String sql = "select * from " + ctable + " where id='" + searchInfo.getCustomerId() + "' limit 1";
        CustomerInfo cinfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        if (cinfo != null) {
            searchInfo.setCustomerName(cinfo.getCustomerName());
            searchInfo.setCustomerPhone(cinfo.getCustomerPhone());
        }
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getSinglePerson())) {
            params.put("singlePerson", searchInfo.getSinglePerson());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerId())) {
            params.put("customerId", searchInfo.getCustomerId());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getLogtype())) {
            params.put("logtype", searchInfo.getLogtype());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getLogList(page, params);
    }

    public int updateReservation(String customerPhone, String projectData, String endTimeValue, String treatmentUnit,String fieldTc, String doctor, String remarks, String reservationsId,
                                 String startTimeValue, String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中'  " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话'  " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //改变详情表状态
        String sql = "update " + ctable + "  set appointment_pay_service= '" + projectData + "'," +
                " field_tc='" + fieldTc + "',query_tc='" + fieldTc + "',doctor='" + doctor + "',remarks_reservation='" + remarks
                + "',start_time='" + startTimeValue + "',end_time='" + endTimeValue + "',treatment_unit='" + treatmentUnit + "'," +
                " create_appointment_time = '" + DateTimeKit.now() + "'" +
                " where id='" + customerId + "'";
        //插入视图改动日志 修改预约
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约项目：" + projectData + " </br>\n预约患者：" + customerInfo.getCustomerName() + " </br>\n预约时间：" + startTimeValue.substring(0, 16) + " - " + endTimeValue.substring(11, 16) + " " +
                " </br>\n预约状态：预约 </br>\n预约科室：" + treatmentUnit + " </br>\n预约医生：" + doctor + " </br>\n咨询师：" + fieldTc + " </br>\n备注：" + remarks + "'," +
                " '" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("修改预约",abbreviation);
        return sqlMapper.update(sql);
    }

    public int sureAppointment(String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String selsql = "select * from  " + ctable + " where id = '" + customerId + "'";
        CustomerInfo customerIn = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = " ";
        if (("9").equals(customerIn.getArrivalStatus()) || ("10").equals(customerIn.getArrivalStatus())) {
            sql = "update " + ctable + "  set arrival_status ='10' where id = '" + customerId + "'";
        } else {
            sql = "update " + ctable + "  set arrival_status ='6' where id = '" + customerId + "'";
        }
        //插入视图改动日志 预约确定
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','修改预约','" + userId + "','预约', '"
                + customerIn.getCustomerName() + "', '" + customerIn.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：预约->确定','" + customerId + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("确定", abbreviation);
        return sqlMapper.update(sql);
    }

    public int no_sureAppointment(String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + "  set arrival_status ='1' where id = '" + customerId + "'";
        String selsql = "select customer_status,customer_name,Customer_phone from  " + ctable + " where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 取消确定
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','修改预约','" + userId + "','预约', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：确定->取消确定','" + customerId + "');";
        sqlMapper.insert(sq11);
        return sqlMapper.update(sql);
    }

    public int cancelAppointmentok(String customerId, String remarks,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + " set arrival_status='8',status='取消预约',remarks='" + remarks + "' where id = '" + customerId + "'";
        String selsql = "select id, a.customer_name,a.customer_phone,a.reservations_id from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 取消预约
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,remarks) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','取消预约','" + userId + "','取消预约', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：预约->取消预约','" + customerId + "','" + remarks + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("取消预约", abbreviation);
        return sqlMapper.update(sql);
    }

    //预约视图 点击挂号
    public int regresses(String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String mtable = abbreviation + "medicalrecord_info";
        String selectiscom = "select id from " + ctable + " " +
                "where id='" + customerId + "' and left(start_time,10)>left(now(),10)";
        List<CustomerInfo> iscomList = sqlMapper.selectList(selectiscom, CustomerInfo.class);
        if (iscomList.size() > 0) {
            return 1;
        } else {
            String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                    "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                    "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName," +
                    "a.* from  " + ctable + " a where id = '" + customerId + "'";
            CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
            String customerStatus = customerPublicService.getNewCustomerStatus(customerInfo.getReservationsId(),abbreviation);
            String sql = "";
            String selectsql1 = "select id,status,follower,archiving,belonger,szm,first_visit_date from reservations_info a where a.id='" + customerInfo.getReservationsId() + "' limit 0,1";
            ReservationsInfo reservationsInfo = sqlMapper.selectOne(selectsql1, ReservationsInfo.class);
            String medicalRecordNumber = "";
            String scarchiving = "";
            if (reservationsInfo != null) {
                if (StringUtil.isNotEmpty(reservationsInfo.getFollower())) {
                    scarchiving = reservationsInfo.getFollower();
                } else {
                    scarchiving = reservationsInfo.getArchiving();
                }
                if ("预约".equals(reservationsInfo.getStatus())) {
                    medicalRecordNumber = customerPublicService.getMedicalRecordNumbers(customerInfo.getCustomerPhone(), mtable);
                    String sql1 = "update reservations_info a set a.status='到店', a.arrival_store='" + outpatientName + "'," +
                            "a.belonger='" + scarchiving + "', a.identification_number='" + medicalRecordNumber + "',a.first_visit_date='" + DateTimeKit.now() + "'" +
                            " where id='" + customerInfo.getReservationsId() + "'";
                    sqlMapper.update(sql1);
                    String sql2 = "update reservations_infosc a set a.status='到店', a.arrival_store='" + outpatientName + "'," +
                            "a.belonger='" + scarchiving + "', a.identification_number='" + medicalRecordNumber + "',a.first_visit_date='" + DateTimeKit.now() + "'" +
                            " where id='" + customerInfo.getReservationsId() + "'";
                    sqlMapper.update(sql2);
                }
            } else {
                medicalRecordNumber = customerPublicService.getMedicalRecordNumbers(customerInfo.getCustomerPhone(), mtable);
                customerInfo.setIdentificationNumber(medicalRecordNumber);
                customerInfo.setOutpatientName(outpatientName);
                customerInfo.setUserId(userId);
                customerInfo.setUserName(userName);
                this.insert(customerInfo);
            }
            String selectsql2 = "select * from distinction_customer_status " +
                    " where reservations_id='" + customerInfo.getReservationsId() + "' " +
                    "and arrival_store='" + outpatientName + "' and status=1 ";
            List<CustomerInfo> cInfo2 = sqlMapper.selectList(selectsql2, CustomerInfo.class);
            if (cInfo2.size() == 0) {  //第一次来本店
                String ctablefom = abbreviation + "customer_info";
                String sqld = "select id,abbreviation from distinction_customer_status " +
                        " where reservations_id ='" + customerInfo.getReservationsId() + "' and status=1 order by add_time desc limit 0,1 ";
                Dept infod = sqlMapper.selectOne(sqld, Dept.class);
                if (infod != null) {   //没来过本店，去过另一家店
                    ctablefom = infod.getAbbreviation() + "customer_info";
                    String sqld1 = "select id as id,medical_record_number as medicalRecordNumber from " + ctablefom + " where reservations_id ='" + customerInfo.getReservationsId() + "' order by arrival_time desc limit 0,1 ";
                    CustomerInfo infod1 = sqlMapper.selectOne(sqld1, CustomerInfo.class);
                    medicalRecordNumber = infod1.getMedicalRecordNumber();
                } else if ("".equals(medicalRecordNumber)) {
                    medicalRecordNumber = customerPublicService.getMedicalRecordNumbers(customerInfo.getCustomerPhone(), mtable);
                }
                String sqld2 = "delete from distinction_customer_status " +
                        " where reservations_id='" + customerInfo.getReservationsId() + "' " +
                        "and arrival_store='" + outpatientName + "' and status=2 ";
                sqlMapper.delete(sqld2);
                String sql1 = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                        " values('" + customerInfo.getReservationsId() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone()
                        + "','" + outpatientName + "','" + abbreviation + "','未成交','"
                        + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + medicalRecordNumber + "') ";
                sqlMapper.insert(sql1);
                sql = "update " + ctable + "  set arrival_status ='2',medical_record_number='" + medicalRecordNumber + "'," +
                        "scarchiving='" + scarchiving + "',status='到店',szm=firstconcats('" + customerInfo.getCustomerName() + "') ,customer_status='" + customerStatus + "'," +
                        "arrival_time='" + DateTimeKit.now() + "',first_visit_date='" + DateTimeKit.now() + "' where id = '" + customerId + "' ";
            } else {  //复诊
                if ("新客首次".equals(customerStatus)) {
                    customerStatus = "新客首次";
                } else if ("新客多次".equals(customerStatus)) {
                    customerStatus = "新客多次";
                } else if ("新客N次".equals(customerStatus)) {
                    customerStatus = "新客N次";
                } else {
                    customerStatus = "复诊";
                }
                String sqlgetm = "select id,medical_record_number,phone_shar_ship from " + ctable + " " +
                        " where customer_phone='" + customerInfo.getCustomerPhone() + "' and reservations_id='" + customerInfo.getReservationsId() + "'" +
                        " and left(arrival_time,10)<'" + DateTimeKit.now().substring(0, 10) + "' limit 0,1";
                CustomerInfo customerInfo2 = sqlMapper.selectOne(sqlgetm, CustomerInfo.class);
                if (customerInfo2 != null) {
                    sql = "update " + ctable + "  set arrival_status ='2',status='到店',phone_shar_ship='" + customerInfo2.getPhoneSharShip() + "'," +
                            "medical_record_number='" + customerInfo2.getMedicalRecordNumber() + "',status='到店',customer_status='" + customerStatus + "'," +
                            "arrival_time='" + DateTimeKit.now() + "' where id = '" + customerId + "' ";
                } else {
                    String sqlgetm2 = "select id,medical_record_number,phone_shar_ship from " + ctable + " " +
                            " where customer_name='" + customerInfo.getCustomerName() + "' and reservations_id='" + customerInfo.getReservationsId() + "' limit 0,1";
                    CustomerInfo customerInfo22 = sqlMapper.selectOne(sqlgetm2, CustomerInfo.class);
                    sql = "update " + ctable + "  set arrival_status ='2',status='到店',phone_shar_ship='" + customerInfo22.getPhoneSharShip() + "'," +
                            "scarchiving='" + scarchiving + "',medical_record_number='" + customerInfo22.getMedicalRecordNumber() + "',status='到店',customer_status='" + customerStatus + "'," +
                            "arrival_time='" + DateTimeKit.now() + "' where id = '" + customerId + "' ";
                }
            }
            //根据预约ID 查询最近一次到店时间的患者
            CustomerInfo info = customerPublicService.firstVisitInformations(customerInfo.getReservationsId()); //首次到店的患者信息
            if (info != null) {
                if (info.getReferralerPerson() == null || info.getReferralerPerson() == "" || info.getReferralerPersoned() == null || info.getReferralerPersoned() == "" || info.getReferralerTime() == null || info.getReferralerTime() == "" || info.getReferralerRemarks() == null || info.getReferralerRemarks() == "") {
                    info.setReferralerPerson("null");
                    info.setReferralerPersoned("null");
                    info.setReferralerRemarks("null");
                    info.setReferralerTime(null);
                } else {
                    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                    Date dt1 = null;
                    Date dt2 = null;
                    try {
                        dt1 = df.parse(DateTimeKit.now().substring(0, 10));
                        dt2 = df.parse(info.getReferralerTime().substring(0, 10));
                        if (dt1.getTime() - dt2.getTime() > 30 * 24 * 60 * 60 * 1000L) {
                            info.setReferralerPerson("null");
                            info.setReferralerPersoned("null");
                            info.setReferralerRemarks("null");
                            info.setReferralerTime(null);
                        } else {
                            info.setReferralerPerson(info.getReferralerPerson());
                            info.setReferralerPersoned(info.getReferralerPersoned());
                            info.setReferralerTime(info.getReferralerTime());
                            info.setReferralerRemarks(info.getReferralerRemarks());
                        }
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                String referralerPerson = "";
                String referralerPersoned = "";
                String referralerTime = "";
                String referralerRemarks = "";
                String marketFilingTime = "";
                if (reservationsInfo.getFirstVisitDate() == null || reservationsInfo.getFirstVisitDate() == "") {
                    marketFilingTime = "null";
                } else {
                    marketFilingTime = "'" + customerInfo.getFirstVisitDate() + "'";
                }
                if (StringUtil.isNotEmpty(info.getReferralerPerson())) {
                    referralerPerson = "'" + info.getReferralerPerson() + "'";
                } else {
                    referralerPerson = "null";
                }
                if (StringUtil.isNotEmpty(info.getReferralerPersoned())) {
                    referralerPersoned = "'" + info.getReferralerPersoned() + "'";
                } else {
                    referralerPersoned = "null";
                }
                if (StringUtil.isNotEmpty(info.getReferralerTime())) {
                    referralerTime = "'" + info.getReferralerTime() + "'";
                } else {
                    referralerTime = null;
                }
                if (StringUtil.isNotEmpty(info.getReferralerRemarks())) {
                    referralerRemarks = "'" + info.getReferralerRemarks() + "'";
                } else {
                    referralerRemarks = "null";
                }
                String updateSql = "update " + ctable + " a set a.referraler_person=" + referralerPerson + ",a.referraler_personed=" + referralerPersoned + "," +
                        " a.referraler_time=" + referralerTime + ",a.referraler_remarks=" + referralerRemarks + ",market_filing_time="+marketFilingTime+" where id='" + customerId + "' ";
                sqlMapper.update(updateSql);
            }
            //插入视图改动日志 点击挂号
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + userName + "','修改预约','" + userId + "','挂号', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" + outpatientName +
                    "','预约状态：" + customerInfo.getArrivalStatusName() + "->挂号','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.insert(sq11);
            sqlMapper.update(sql);
            webSocket.sendMessageViews("挂号", abbreviation);
            return 0;
        }
    }

    public int insert(CustomerInfo info) {
        info.setStatus("到店");
        info.setArrivalStore(info.getOutpatientName());
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setDepartment(info.getOneChannel());
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getWechatNumber()) || info.getWechatNumber() == null) {
            info.setWechatNumber("");
        }
        if ("null".equals(info.getAppointmentPayService()) || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getFieldTc()) || info.getFieldTc() == null) {
            info.setFieldTc("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String sql = "INSERT INTO reservations_info (`id`,`huifang`,`department`,`appointment_time`,`tc_name`,`first_visit_date`,`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`, `customer_age`, `qq_number`," +
                " `wechat_number`,`other_contact`, `country`, `province`, `cities`, `region`, `quarters`," +
                " `marital_status`, `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`, " +
                " `one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`,`remarks`, `status`,`appointment_arrival_time`," +
                "`is_supplement`,identification_number,arrival_store,szm,search_field,customer_sex_head) VALUES" +
                "('" + info.getReservationsId() + "','" + info.getHuifang() + "','" + info.getDepartment() + "','" + DateTimeKit.now() + "','" + info.getFieldTc() + "','" + DateTimeKit.now() + "','"
                + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getArrivalStore() + "','" + info.getCustomerName() + "','"
                + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getQqNumber() + "','" + info.getWechatNumber() + "','"
                + info.getOtherContact() + "','" + info.getCountry() + "','" + info.getProvince() + "','" + info.getCities() + "','" + info.getRegion() + "','" + info.getAddress() + "',"
                + info.getMaritalStatus() + ",'" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + info.getUserName()+ "','" + info.getUserName()+ "','"
                + DateTimeKit.now() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','"
                + info.getThrChannel() + "','"+ info.getRemarks() + "', '" + info.getStatus() + "','" + DateTimeKit.now() + "','否','" + info.getMedicalRecordNumber() + "','"+ info.getOutpatientName()
                + "',firstconcats('" + info.getCustomerName() + "'),concat('" + info.getCustomerName() + "','" + info.getCustomerPhone() + "',firstconcats('" + info.getCustomerName() + "')," +
                "'" + info.getCustomerPhone2() + "','" + info.getWechatNumber() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "'),'"
                +customerPublicService.getSexPt(info)+"')";
        return sqlMapper.insert(sql);
    }


    public int startTreatment(String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id,a.arrival_status from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        if (("1").equals(customerInfo.getArrivalStatus()) || ("6").equals(customerInfo.getArrivalStatus())) { //未挂号患者医生点了接诊
            this.regresses(customerId,abbreviation,outpatientName,userName,userId); //先走挂号方法
        }
        String sql = "update " + ctable + "  set arrival_status ='3' where id = '" + customerId + "'";
        //插入视图改动日志 开始治疗
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                "','预约状态：" + customerInfo.getArrivalStatusName() + "->开始治疗','" + customerId + "');";
        sqlMapper.update(sql);
        webSocket.sendMessageViews("开始治疗", abbreviation);
        return sqlMapper.insert(sq11);
    }

    public int completionTreatment(String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + "  set arrival_status ='4' where id = '" + customerId + "'";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 完成治疗
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                "','预约状态：" + customerInfo.getArrivalStatusName() + "->完成治疗','" + customerId + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("完成治疗", abbreviation);
        return sqlMapper.update(sql);
    }

    public int alreadyLeave(String customerId,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String sql = "update " + ctable + "  set arrival_status ='5' where id = '" + customerId + "'";
        String selsql = "select id, (case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        //插入视图改动日志 已离开
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                "','预约状态：" + customerInfo.getArrivalStatusName() + "->已离开','" + customerId + "','" + customerInfo.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("已离开", abbreviation);
        return sqlMapper.update(sql);
    }

    public int fallbackAction(String customerId,String arrivalStatus,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中' " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话' " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        if (arrivalStatus.equals("5")) {
            String sql = "update " + ctable + "  set arrival_status ='4' where id = '" + customerId + "'";
            sqlMapper.update(sql);
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                    "','预约状态：离开->完成治疗','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.insert(sq11);
        } else if (arrivalStatus.equals("4")) {
            String sql = "update " + ctable + "  set arrival_status ='3' where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                    "','预约状态：完成治疗->就诊中','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.update(sql);
            sqlMapper.insert(sq11);
        } else if (arrivalStatus.equals("3")) {
            String sql = "update " + ctable + "  set arrival_status ='2' where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                    "','预约状态：就诊中->到店','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.update(sql);
            sqlMapper.insert(sq11);
        } else if (arrivalStatus.equals("7")) {
            String sql = "update " + ctable + "  set arrival_status ='1' where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                    "','预约状态：失约->预约','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.insert(sq11);
            sqlMapper.update(sql);
        } else if (arrivalStatus.equals("2")) {
            String sql = "update " + ctable + "  set arrival_status ='1',status ='预约',arrival_time=null,customer_status =null where id = '" + customerId + "'";
            //插入视图改动日志 回退
            String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                    " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" +userName + "','修改预约','" + userId + "','" + customerInfo.getCustomerStatus() + "', '"
                    + customerInfo.getCustomerName() + "', '" + customerInfo.getCustomerPhone() + "','成功','" +outpatientName+
                    "','预约状态：到店->预约','" + customerId + "','" + customerInfo.getReservationsId() + "');";
            sqlMapper.update(sql);
            sqlMapper.insert(sq11);
            String sql2 = "select id from reservations_info where id='" + customerInfo.getReservationsId() + "' " +
                    "and left(first_visit_date,10) = '" + (DateTimeKit.now()).substring(0, 10) + "' and one_channel!='直接上门' and one_channel!='老带新' limit 0,1";
            ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql2, ReservationsInfo.class);
            if (reservationsInfo != null) {
                String updatesql = "update reservations_info set status='预约',first_visit_date=null where id='" + customerInfo.getReservationsId() + "'";
                sqlMapper.update(updatesql);
                String delsql = "delete from distinction_customer_status where reservations_id='" + customerInfo.getReservationsId() + "'" +
                        " and arrival_store='" +outpatientName+ "' and status=1 ";
                sqlMapper.delete(delsql);
            } else {
                String sql3 = "select id from reservations_info where id='" + customerInfo.getReservationsId() + "' " +
                        "and left(first_visit_date,10) = '" + (DateTimeKit.now()).substring(0, 10) + "' and (one_channel='直接上门' or one_channel='老带新') limit 0,1";
                ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
                if (reservationsInfo1 != null) {
                    String deletesql = "delete from reservations_info where id='" + customerInfo.getReservationsId() + "'";
                    sqlMapper.delete(deletesql);
                    String delsql = "delete from distinction_customer_status where reservations_id='" + customerInfo.getReservationsId() + "'" +
                            " and arrival_store='" +outpatientName+ "' and status=1 ";
                    sqlMapper.delete(delsql);
                }
            }
        } else if (arrivalStatus.equals("1")) {
            String sql = "update " + ctable + "  set arrival_status ='1' where id = '" + customerId + "'";
            sqlMapper.update(sql);
        }
        webSocket.sendMessageViews("回退", abbreviation);
        return 1;
    }

    public List<CustomerInfo> getMakeNumer(String reservationsId, String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql = "" +
                "(select '今天' as name,left(start_time,10) as yearday,substring(start_time,11,6) as startTime,substring(end_time,11,6) as endTime ," +
                "a.appointment_pay_service as appointmentPayService,a.doctor as doctor,a.id,a.reservations_id as reservationsId  " +
                "from " + ctable + " a where arrival_status!=8 and reservations_id='" + reservationsId + "' and left(start_time,10)=left(now(),10) ) " +
                "union all" +
                "(select '之后' as name,left(start_time,10) as yearday,substring(start_time,11,6) as startTime,substring(end_time,11,6) as endTime ," +
                "a.appointment_pay_service as appointmentPayService,a.doctor as doctor,a.id,a.reservations_id as reservationsId " +
                "from " + ctable + " a where arrival_status!=8 and reservations_id='" + reservationsId + "' and left(start_time,10)>left(now(),10) )" +
                "union all" +
                "(select '之前' as name,left(start_time,10) as yearday,substring(start_time,11,6) as startTime,substring(end_time,11,6) as endTime ," +
                "a.appointment_pay_service as appointmentPayService,a.doctor as doctor,a.id,a.reservations_id as reservationsId " +
                "from " + ctable + " a  where arrival_status!=8 and reservations_id='" + reservationsId + "' and left(start_time,10)<left(now(),10) );";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql, CustomerInfo.class);
        return customerInfoList;
    }

    public List<CustomerInfo> getCustomerByStore(String reach, String pageStart, String pageSize,String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql2 = " ";
        if (StringUtils.isNotBlank(reach)) {
            sql2 = "  and ( t.customer_name like CONCAT('%" + reach + "%') or  t.customer_phone like CONCAT('%" + reach + "%') or " +
                    "  t.medical_record_number like CONCAT('%" + reach + "%') or t.szm like CONCAT('%" + reach + "%')) ";
        }
        String sql = "select id,customerName, customerAge,customerPhone,reservationsId, sex,arrivalStore from (select t.id as id,t.customer_name as customerName,t.customer_age as customerAge," +
                "t.customer_phone as customerPhone, t.reservations_id as reservationsId, " +
                "  ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex,t.arrival_store as arrivalStore   from " + ctable + " t " +
                "where 1=1 " + sql2 + " and t.arrival_status not in (1,6,7,8,9,10) ) t1  group by reservationsId " +
                "limit 0," + Integer.parseInt(pageStart) * Integer.parseInt(pageSize);
        List<CustomerInfo> customerInfo = sqlMapper.selectList(sql, CustomerInfo.class);
        return customerInfo;
    }

    public int getdcsByReservationId(String id){
        String abbreviationSql = "select t.id from distinction_customer_status t where t.reservations_id = '" + id + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        if (abbreviationList.size() > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    public String temporaryCustomer(String customerName,String customerAge,String customerPhone,String customerSex,String phoneSharShip,String abbreviation){
        String ctable = abbreviation + "customer_info";
        List<ReservationsInfo> existList = this.getCustomerPhoneCounts(customerPhone);
        if (existList.size()>0) {
            return "0,";
        } else {
            String ageRange = "";
            if (!("").equals(customerAge)) {
                for (int i = 0; i < 11; i++) {
                    if (Integer.parseInt(customerAge) >= (i * 10) && Integer.parseInt(customerAge) <= (i * 10 + 9)) {
                        if (i < 10) {
                            ageRange = (i * 10) + "-" + (i * 10 + 9);
                        } else {
                            ageRange = "99+";
                        }
                    }
                }
                if (Integer.parseInt(customerAge) >= 100) {
                    ageRange = "99+";
                }
            } else {
                ageRange = "99+";
                customerAge = null;
            }
            if (phoneSharShip == null || phoneSharShip == "") {
                phoneSharShip = "本人";
            }
            CustomerInfo info = new CustomerInfo();
            info.setCustomerAge(Integer.parseInt(customerAge));
            info.setCustomerSex(Integer.parseInt(customerSex));
            if (!"本人".equals(phoneSharShip)) {
                String sql1 = "select * from " + ctable + " where customer_phone='" + customerPhone + "' or customer_phone2='" + customerPhone + "'";
                List<CustomerInfo> infoList = sqlMapper.selectList(sql1, CustomerInfo.class);
                if (infoList.size() > 0) {
                    int count = infoList.size();
                    customerPhone = customerPhone + "+" + count;
                    String id = customerPublicService.getId(abbreviation);
                    String sql = "insert into " + ctable + " (id,customer_name,customer_age,age_range,customer_phone,customer_sex," +
                            "status,update_time,filing_time,arrival_status,diagnosis_status,deal_status,isdeal_status,szm,phone_shar_ship,customer_sex_head) " +
                            "values('" + id + "','" + customerName + "'," + customerAge + ",'" + ageRange + "','" + customerPhone + "','" + customerSex + "'," +
                            "'预约','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','9','未看诊','未开单','未成交',firstconcats('" + customerName + "'),'" + phoneSharShip + "'," +
                            "'" + customerPublicService.getSexPt(info) + "')";
                    return sqlMapper.insert(sql) + "," + id;
                } else {
                    String id = customerPublicService.getId(abbreviation);
                    String sql = "insert into " + ctable + " (id,customer_name,customer_age,age_range,customer_phone,customer_sex," +
                            "status,update_time,filing_time,arrival_status,diagnosis_status,deal_status,isdeal_status,szm,phone_shar_ship,customer_sex_head) " +
                            "values('" + id + "','" + customerName + "'," + customerAge + ",'" + ageRange + "','" + customerPhone + "','" + customerSex + "'," +
                            "'预约','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','9','未看诊','未开单','未成交',firstconcats('" + customerName + "'),'" + phoneSharShip + "'," +
                            "'" + customerPublicService.getSexPt(info) + "')";
                    return sqlMapper.insert(sql) + "," + id;
                }
            } else {
                String sql1 = "select * from " + ctable + " where customer_phone='" + customerPhone + "' or customer_phone2='" + customerPhone + "'";
                List<CustomerInfo> infoList = sqlMapper.selectList(sql1, CustomerInfo.class);
                if (infoList.size() > 0) {
                    return "0,";
                } else {
                    String id = customerPublicService.getId(abbreviation);
                    String sql = "insert into " + ctable + " (id,customer_name,customer_age,age_range,customer_phone,customer_sex," +
                            "status,update_time,filing_time,arrival_status,diagnosis_status,deal_status,isdeal_status,szm,phone_shar_ship,one_channel,one_medium,customer_sex_head) " +
                            "values('" + id + "','" + customerName + "'," + customerAge + ",'" + ageRange + "','" + customerPhone + "','" + customerSex + "'," +
                            "'预约','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','9','未看诊','未开单','未成交',firstconcats('" + customerName + "')," +
                            "'" + phoneSharShip + "','直接上门','直接上门'," +
                            "'" + customerPublicService.getSexPt(info) + "')";
                    return sqlMapper.insert(sql) + "," + id;
                }
            }
        }
    }

    public int addTemporaryCustomer( String projectData, String duration, String treatmentUnit,String fieldTc, String doctor, String remarks,
                                     String startTimeValue, String customerId,String abbreviation,String outpatientName,String userName,String userId) throws ParseException {
        String ctable = abbreviation + "customer_info";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = formatter.parse(startTimeValue);
        Date date1 = new Date(date.getTime() + Integer.parseInt(duration) * 60 * 1000);
        String endTimeValue = formatter.format(date1);
        Random rand = new Random();
        int randomNumber = rand.nextInt(9999);
        String selsql = "select id,a.arrival_status,(case a.arrival_status when (1 or 9) then '预约' when 2 then '到店' when 3 then '就诊中'  " +
                "when 4 then '完成治疗' when 5 then '离开' when (6 or 10) then '已打电话'  " +
                "when 7 then '失约' when 8 then '取消预约' else '其他' end) as arrivalStatusName,a.customer_name,a.customer_phone,a.customer_status," +
                "a.reservations_id from  " + ctable + " a where id = '" + customerId + "'";
        CustomerInfo cinfo = sqlMapper.selectOne(selsql, CustomerInfo.class);
        String sql = "update " + ctable + " set appointment_pay_service='" + projectData + "',update_time='" + DateTimeKit.now() + "'," +
                " update_people='" + userName + "',archiving='" + userName + "'," +
                " field_tc='" + fieldTc + "',query_tc='" + fieldTc + "',doctor='" + doctor + "',remarks_reservation='" + remarks + "',start_time='" + startTimeValue + "'," +
                " end_time='" + endTimeValue + "',create_appointment_time='" + DateTimeKit.now() + "'," +
                " treatment_unit='" + treatmentUnit + "',medical_record_number='LS" + randomNumber + "' " +
                " where id='" + customerId + "'";
        //插入视图改动日志 新诊预约
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','新增预约','" + userId+ "','" + cinfo.getCustomerStatus() + "', '"
                + cinfo.getCustomerName() + "', '" + cinfo.getCustomerPhone() + "','成功','" + outpatientName+
                "','预约项目：" + projectData + " </br>\n预约患者：" + cinfo.getCustomerName() + "</br>\n预约时间：" + startTimeValue.substring(0, 16) + " - " + endTimeValue.substring(11, 16) + " " +
                "</br>\n预约状态：预约 </br>\n预约科室：" + treatmentUnit + " </br>\n预约医生：" + doctor + " </br>\n咨询师：" + fieldTc + " </br>\n备注：" + remarks + " '," +
                "'" + customerId + "','" + cinfo.getReservationsId() + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("新增预约", abbreviation);
        return sqlMapper.update(sql);
    }

    public int insertCustomerStatus(CustomerInfo info,String abbreviation,String outpatientName) {
        String seqsql="select id from distinction_customer_status where abbreviation='"+abbreviation+"' and status=2 and reservations_id='"+info.getReservationsId()+"' limit 1";
        QueryInfo queryInfo= sqlMapper.selectOne(seqsql,QueryInfo.class);
        if(queryInfo!=null){
            String desql="delete from distinction_customer_status where abbreviation='"+abbreviation+"' and status=2 and reservations_id='"+info.getReservationsId()+"'";
            sqlMapper.delete(desql);
        }
        String sql = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number) " +
                " values('" + info.getReservationsId() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone()
                + "','" +outpatientName+ "','" +abbreviation+ "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "','" + info.getMedicalRecordNumber() + "') ";
        return sqlMapper.insert(sql);
    }

    //新诊到店
    public int insertxz(CustomerInfo info,String abbreviation,String outpatientName,String userName) {
        String ctable = abbreviation + "customer_info";
        String birthday = "";
        String auditorTime = "";
        String phoneSharShip = "";
        String marketFilingTime = "";
        if (info.getBirthday() == null || info.getBirthday() == "") {
            birthday = "null";
        } else {
            birthday = "'" + info.getBirthday() + "'";
        }
        if (info.getAuditorTime() == null || info.getAuditorTime() == "") {
            auditorTime = "null";
        } else {
            auditorTime = "'" + info.getAuditorTime() + "'";
        }
        if (info.getMarketFilingTime() == null || info.getMarketFilingTime() == "") {
            marketFilingTime = "null";
        } else {
            marketFilingTime = "'" + info.getMarketFilingTime() + "'";
        }
        if (info.getPhoneSharShip() == null || info.getPhoneSharShip() == "") {
            phoneSharShip = "'本人'";
        } else {
            phoneSharShip = "'" + info.getPhoneSharShip() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
            if (info.getCustomerAge() >= 100) {
                info.setAgeRange("99+");
            }
        }
        info.setArrivalStore(outpatientName);
        Double actualAccountBalance = 0.00;
        Double giveAccountBalance = 0.00;
        Double accountArrears = 0.00;
        if (info.getAddress() == "" || info.getAddress() == null) {
            info.setAddress("");
        }
        if (info.getAppointmentPayService() == "" || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getOtherContact()) || info.getOtherContact() == null) {
            info.setOtherContact("");
        }
        String sql1 = "select * from " + ctable + " where id='" + info.getId() + "'";
        CustomerInfo ccinfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String remarksReservation = "";
        if (ccinfo.getRemarksReservation() == null || ccinfo.getRemarksReservation() == "") {
            remarksReservation = "null";
        } else {
            remarksReservation = "'" + ccinfo.getRemarksReservation() + "'";
        }
        String sql = "INSERT INTO `" + ctable + "` (`id`,`first_visit_date`,`actual_account_balance`,`give_account_balance`,`account_arrears`," +
                "`age_range`,`reservations_id`,`appointment_pay_service`,`arrival_store`,`phone_shar_ship`," +
                "`deal_status`, `customer_name`, `pseudonym`, " +
                "`customer_phone`,`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                "`field_tc`, `query_tc`, `customer_sex`, `customer_age`, `birthday`,`address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`,`customer_status`, `number_of_audits`, `auditor_time`, `final_auditor`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk`,`update_people`,`update_time`," +
                " `filing_time`, `arrival_time`, `remarks`,remarks_reservation, `status`, `diagnosis_status`,`arrival_status`,`doctor`,`start_time`,`end_time`," +
                "`create_appointment_time`,yellow_number,scarchiving,treatment_unit,szm,customer_sex_head,market_filing_time) VALUES" +
                "('" + info.getCustomerId() + "','" + DateTimeKit.now() + "'," + actualAccountBalance + "," + giveAccountBalance + "," + accountArrears +
                ",'" + info.getAgeRange() + "','" + info.getReservationsId() + "','" + info.getAppointmentPayService() + "','" + info.getArrivalStore() + "'," + phoneSharShip
                + ",'" + ccinfo.getDealStatus() + "','" + info.getCustomerName().trim() + "','" + info.getPseudonym() +
                "','" + info.getCustomerPhone() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "'" +
                ",'" + info.getTwoChannel() + "','" + info.getThrChannel() + "'" +
                ",'" + info.getFieldTc() + "','" + info.getFieldTc() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + "," + birthday +
                ",'" + info.getAddress() + "','" + info.getReferee() + "','" + info.getRefereeShip() + "','" + info.getIdCardNo() +
                "','" + info.getFixedTelephone() + "'," + info.getIsHaveChildren() + "," + "'" + info.getEmail() + "','" + info.getZipCode() +
                "','" + info.getCustomerStatus() + "'," + info.getNumberOfAudits() + "," + auditorTime + ",'" + info.getFinalAuditor() +
                "','" + info.getNonTransactionLevel() + "', '" + info.getTransactionLevel() + "','" + info.getCharacters() +
                "','" + info.getConsumptionHabits() + "'," + info.getIsReturnVisit() + ",'" + info.getCauseOfNoReturnVisit() + "','" + info.getConsumAbility() +
                "','" + info.getQqNumber() + "', '" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() +
                "','" + info.getProvince() + "' ,'" + info.getCities() + "','" + info.getRegion() + "','" + info.getMedicalRecordNumber() + "','" + info.getMaritalStatus() + "'," +
                "'" + info.getOccupation() + "','" + ccinfo.getArchiving() + "','" + userName +
                "','" + userName + "','" + DateTimeKit.now() + "','" + ccinfo.getFilingTime() + "','" + DateTimeKit.now() + "'" +
                ",'" + info.getRemarks() + "'," + remarksReservation + ",'到店','" + ccinfo.getDiagnosisStatus() + "','2','" + ccinfo.getDoctor() + "','"
                + ccinfo.getStartTime() + "','" + ccinfo.getEndTime() + "','" + ccinfo.getCreateAppointmentTime() + "','" + info.getYellowNumber() + "','"
                + info.getScarchiving() + "','"
                + info.getTreatmentUnit() + "',firstconcats('" + info.getCustomerName() + "'),'"
                + customerPublicService.getSexPt(info)+"'," + marketFilingTime + ");";
        String upda = "update reservations_info set customer_name='" + info.getCustomerName() + "',customer_sex='" + info.getCustomerSex() + "'," +
                "customer_age=" + info.getCustomerAge() + ",customer_sex_head='"+customerPublicService.getSexPt(info)+"'," +
                "province='" + info.getProvince() + "',cities='" + info.getCities() + "'," +
                "region='" + info.getRegion() + "',address='" + info.getAddress() + "',occupation='" + info.getOccupation() + "'," +
                "identification_number='" + info.getMedicalRecordNumber() + "'," +
                "status='到店',first_visit_date='" + DateTimeKit.now() + "',arrival_store='" + outpatientName+ "' " +
                " where id='" + info.getReservationsId() + "'";
        sqlMapper.update(upda);
        String upca = "update " + ctable + " set customer_name ='" + info.getCustomerName() + "'" +
                ",birthday='" + info.getBirthday() + "'" +
                ",age_range= " +
                "(case " +
                " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<10) then '0-9' " +
                " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<20) then '10-19' " +
                " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<30) then '20-29' " +
                " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<40) then '30-39' " +
                " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<50) then '40-49' " +
                " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<60) then '50-59' " +
                " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<70) then '60-69' " +
                " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<80) then '70-79' " +
                " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<90) then '80-89' " +
                " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y'))<100) then '90-99' " +
                " else '99+' end)" +
                ",customer_age=(DATE_FORMAT(FROM_DAYS(TO_DAYS(start_time)-TO_DAYS('" + info.getBirthday() + "')),'%Y')),customer_sex='" + info.getCustomerSex() + "'" +
                ",medical_record_number='" + info.getMedicalRecordNumber() + "',referee='" + info.getReferee() + "',referee_ship='" + info.getRefereeShip() + "'" +
                ",szm=firstconcats('" + info.getCustomerName() + "'),yellow_number='" + info.getYellowNumber() + "',customer_name='" + info.getCustomerName() + "',province='" + info.getProvince() + "',cities='" + info.getCities() + "',region='" + info.getRegion() + "',address='" + info.getAddress() + "'" +
                ",occupation='" + info.getOccupation() + "',consum_ability='" + info.getConsumAbility() + "',consumption_habits='" + info.getConsumptionHabits() +
                "',id_card_no='" + info.getIdCardNo() + "',fixed_telephone='" + info.getFixedTelephone() + "',qq_number='" + info.getQqNumber() +
                "',wechat_Number='" + info.getWechatNumber() + "',email='" + info.getEmail() + "',zip_code='" + info.getZipCode() +
                "',marital_status='" + info.getMaritalStatus() + "',is_have_children='" + info.getIsHaveChildren() + "',characters='" + info.getCharacters() +
                "' where reservations_id ='" + info.getReservationsId() + "';";
        sqlMapper.update(upca);
        String upda2 = "update customer_reservations_update_log set customer_id='" + info.getCustomerId() + "'" +
                " where customer_id='" + ccinfo.getId() + "'";
        sqlMapper.update(upda2);
        sqlMapper.insert(sql);
        String desql = "delete from " + ctable + " where id='" + ccinfo.getId() + "'";
        return sqlMapper.delete(desql);
    }

    public int addTemporaryCustomerOK(CustomerInfo info,String abbreviation,String outpatientName,String userName,String userId){
        String ctable = abbreviation + "customer_info";
        String mtable = abbreviation + "medicalrecord_info";
        String id = "";
        Random rand = new Random();
        int randomNumber = rand.nextInt(999999);
        if (info.getReferee() != null && info.getReferee() != "") {
            id = "ldx" + CommUtils.getUUID() + randomNumber;
//            info.setOneChannel(info.getOneMedium());
//            info.setTwoChannel(info.getTwoMedium());
//            info.setThrChannel(info.getThrMedium());
        } else {
            id = "zd" + CommUtils.getUUID() + randomNumber;
        }
        String customerid = customerPublicService.getId(abbreviation);
        info.setCustomerId(customerid);
        info.setMedicalRecordNumber(customerPublicService.getMedicalRecordNumbers(info.getCustomerPhone(), mtable));
        String phoneSql3 = "select id,filing_time from reservations_info a " +
                " where (customer_phone like '%" + info.getCustomerPhone() + "%' or customer_phone2 like '%" + info.getCustomerPhone() + "%' )";
        List<ReservationsInfo> reservationsInfoList = sqlMapper.selectList(phoneSql3, ReservationsInfo.class);
        if (reservationsInfoList.size() == 0) {   //不是市场人员新建的洁牙预约 ，插入数据到总表
            info.setReservationsId(id);
            this.insert(info);
        } else {   //是市场人员新建的洁牙预约 ，不插入总表，修改为到店
            String upda = "update reservations_infosc set customer_sex='" + info.getCustomerSex() + "'," +
                    " customer_age=" + info.getCustomerAge() + ",province='" + info.getProvince() + "',cities='" + info.getCities() + "'," +
                    " region='" + info.getRegion() + "',address='" + info.getAddress() + "',occupation='" + info.getOccupation() + "'," +
                    " identification_number='" + info.getMedicalRecordNumber() + "',status='到店'," +
                    " first_visit_date='" + DateTimeKit.now() + "',arrival_store='" + outpatientName + "' " +
                    " where id='" + reservationsInfoList.get(0).getId() + "'";
            sqlMapper.update(upda);
            info.setReservationsId(reservationsInfoList.get(0).getId());
            info.setMarketFilingTime(reservationsInfoList.get(0).getFilingTime());
        }
        this.insertCustomerStatus(info,abbreviation,outpatientName);   //加入客户状态表
        this.insertxz(info,abbreviation,outpatientName,userName);   //门诊预约初诊到店挂号
        //插入视图改动日志 新诊挂号
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName+ "','新诊预约点挂号','" + userId+ "','" + info.getCustomerStatus() + "', '"
                + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约状态：预约->挂号','" + info.getCustomerId() + "','" + info.getReservationsId() + "');";
        //插入号修改日志
        String sq12 = "INSERT INTO `customer_medical_number_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" +userName+ "','挂号生成','" + userId+ "','" + info.getCustomerStatus() + "', '"
                + info.getCustomerName() + "', '" + info.getCustomerPhone() + "','成功','" + outpatientName +
                "','生成的病历号为:" + info.getMedicalRecordNumber() + " ','" + info.getCustomerId() + "','" + info.getReservationsId() + "');";
        String upcussql1 = "update " + ctable + " set arrival_status=1 where arrival_status=9 and reservations_id='" + info.getReservationsId() + "';";
        String upcussql2 = "update " + ctable + " set arrival_status=6 where arrival_status=10 and reservations_id='" + info.getReservationsId() + "';";
        sqlMapper.update(upcussql1);
        sqlMapper.update(upcussql2);
        sqlMapper.insert(sq12);
        webSocket.sendMessageViews("新诊预约点挂号",abbreviation);
        return sqlMapper.insert(sq11);
    }

    //市场人员预约复诊洁牙
    public int insertReturnfzjy(String customid, String projectData, String treatmentUnit, String customerPhone, String fieldTc, String tableName, String customerId,
                                String startTimeValue, String endTimeValue, String doctor, String remarks,String abbreviation,String outpatientName,String userName) {
        String ctable = abbreviation + "customer_info";
        String sql = "INSERT INTO " + ctable+ " (`id`,scarchiving,`diagnosis_status`,`first_visit_date`,`actual_account_balance`," +
                "`give_account_balance`,`account_arrears`,`age_range`,`reservations_id`,`appointment_pay_service`," +
                "`arrival_store`,`phone_shar_ship`,`deal_status`, `status`,`customer_name`, `pseudonym`,szm, " +
                "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                " `customer_sex`,customer_sex_head, `customer_age`, `birthday`,  `address`, `referee`, `referee_ship`, `id_card_no`," +
                " `fixed_telephone`, `is_have_children`, `email`, `zip_code`,treatment_unit, " +
                "  `number_of_audits`, `auditor_time`, `final_auditor`,`yhbalance`,`jfbalance`,`money_balance`," +
                "  `non_transaction_level`, `transaction_level`, `characters`, " +
                "`consumption_habits`, `is_return_visit`, `cause_of_no_return_visit`, `consum_ability`," +
                " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                " `province`, `cities`, `region`, `medical_record_number`, " +
                " `marital_status`, `occupation`, `archiving`, `entry_clerk` ,`update_people`," +
                "  `filing_time`," +
                " `customer_phone`,customer_phone2,`field_tc`,`query_tc`,`doctor`,`remarks`,`remarks_reservation`,`start_time`,`end_time`,create_appointment_time,yellow_number )  " +
                " select '" + customid + "',scarchiving,'未看诊',first_visit_date,actual_account_balance," +
                " give_account_balance,account_arrears, " +
                "(case " +
                " WHEN (0<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<10) then '0-9' " +
                " WHEN (10<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<20) then '10-19' " +
                " WHEN (20<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<30) then '20-29' " +
                " WHEN (30<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<40) then '30-39' " +
                " WHEN (40<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<50) then '40-49' " +
                " WHEN (50<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<60) then '50-59' " +
                " WHEN (60<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<70) then '60-69' " +
                " WHEN (70<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<80) then '70-79' " +
                " WHEN (80<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<90) then '80-89' " +
                " WHEN (90<=(DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')+1)) and ((DATE_FORMAT(FROM_DAYS(TO_DAYS(arrival_time)-TO_DAYS(birthday)),'%Y')+1)<100) then '90-99' " +
                " else '99+' end)," +
                " reservations_id,'" + projectData + "'," +
                " '" + outpatientName + "',phone_shar_ship,'未开单','预约',customer_name , pseudonym, szm, " +
                "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                " customer_sex,customer_sex_head, (DATE_FORMAT(FROM_DAYS(TO_DAYS(now())-TO_DAYS(birthday)),'%Y')), birthday, address, referee, referee_ship, id_card_no," +
                " fixed_telephone, is_have_children, email, zip_code,'" + treatmentUnit + "'," +
                "   number_of_audits, auditor_time, final_auditor,0,jfbalance,money_balance," +
                " non_transaction_level,transaction_level,characters," +
                " consumption_habits,is_return_visit,cause_of_no_return_visit,consum_ability," +
                " qq_number,wechat_number,other_contact,country," +
                " province, cities,region,medical_record_number,marital_status," +
                " occupation,'" + userName + "','" + userName + "','" + userName + "', '" + DateTimeKit.now() + "'," +
                " '" + customerPhone + "',customer_phone2,'" + fieldTc + "','" + fieldTc + "','" + doctor + "'," +
                "remarks,'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "',yellow_number " +
                " from " + tableName + " where id='" + customerId + "' ";
        //在另一家创建了新预约
        String sql2 = "insert into distinction_customer_status (reservations_id,customer_name,customer_phone,arrival_store,abbreviation,deal_status,add_time,update_time,medical_record_number,status) " +
                " select reservations_id,customer_name,'" + customerPhone + "','" + outpatientName
                + "','" +abbreviation + "','未成交','" + DateTimeKit.now() + "','" + DateTimeKit.now() + "',medical_record_number,2 " +
                " from " + tableName + " where id='" + customerId + "' ";
        sqlMapper.insert(sql2);
        return sqlMapper.insert(sql);
    }

    public int addCustomerMarket(String customerPhone, String projectData, String duration, String treatmentUnit,String fieldTc, String doctor, String remarks, String reservationsId,
                                 String startTimeValue,String abbreviation,String outpatientName,String userName,String userId) throws ParseException {
        String ctable = abbreviation + "customer_info";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = formatter.parse(startTimeValue);
        Date date1 = new Date(date.getTime() + Integer.parseInt(duration) * 60 * 1000);
        String endTimeValue = formatter.format(date1);
        Random rand = new Random();
        int randomNumber = rand.nextInt(9999);
        String customid = customerPublicService.getId(abbreviation);
        String rsql = "select id,customer_name,customer_phone,archiving from reservations_info where id ='" + reservationsId + "'";
        ReservationsInfo rinfo = sqlMapper.selectOne(rsql, ReservationsInfo.class);
        String sqlc = "select * from " + ctable + " t where reservations_id ='" + reservationsId + "' " +
                "and t.arrival_status in (2,3,4,5) order by arrival_time desc limit 0,1 ";
        CustomerInfo cinfo = sqlMapper.selectOne(sqlc, CustomerInfo.class);
        if (cinfo != null) {  //市场复诊预约洁牙
            this.insertReturnfzjy(customid, projectData, treatmentUnit, customerPhone, fieldTc, ctable, cinfo.getId(),
                    startTimeValue, endTimeValue, doctor, remarks,abbreviation, outpatientName, userName);
        } else {
            String tableName = "";
            String sqld = "select id,abbreviation from distinction_customer_status where reservations_id ='" + reservationsId + "' and status=1  order by add_time desc limit 0,1 ";
            Dept infod = sqlMapper.selectOne(sqld, Dept.class);
            if (infod != null) {   //没来过本店，去过另一家店
                tableName = infod.getAbbreviation() + "customer_info";
                String sqld1 = "select id from " + tableName + " where reservations_id ='" + reservationsId + "' order by arrival_time desc limit 0,1 ";
                CustomerInfo infod1 = sqlMapper.selectOne(sqld1, CustomerInfo.class);
                this.insertReturnfzjy(customid, projectData, treatmentUnit, customerPhone, fieldTc, tableName, infod1.getId(),
                        startTimeValue, endTimeValue, doctor, remarks,abbreviation, outpatientName, userName);
            } else {
                String sql1 = "update reservations_info set identification_number='LS" + randomNumber + "' where id ='" + reservationsId + "'";
                sqlMapper.update(sql1);
                String sql = "INSERT INTO " + ctable + " (`id`,`diagnosis_status`,`field_tc`,`query_tc`,`doctor`,`first_visit_date`," +
                        "`age_range`,`reservations_id`,`appointment_pay_service`," +
                        "`arrival_store`,`phone_shar_ship`,`deal_status`, `customer_name`, medical_record_number, " +
                        "`one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`," +
                        " `customer_sex`, `customer_age`,customer_sex_head, scarchiving, " +
                        " `qq_number`, `wechat_number`, `other_contact`, `country`," +
                        " `province`, `cities`, `region`,`address`," +
                        " `archiving`, `entry_clerk` ,`update_people`," +
                        "  `filing_time`,treatment_unit," +
                        " `customer_phone`,`remarks`,`start_time`,`end_time`,create_appointment_time,arrival_status,status)  " +
                        " select '" + customid + "','未看诊','" + fieldTc + "','" + fieldTc + "','" + doctor + "',first_visit_date," +
                        "  age_range,id,'" + projectData + "'," +
                        " '" + outpatientName + "',phone_shar_ship,'未开单',customer_name ,identification_number," +
                        "one_medium,two_medium,thr_medium, one_channel,two_channel,thr_channel," +
                        " customer_sex,customer_age,customer_sex_head,archiving, " +
                        " qq_number,wechat_number,other_contact,country,quarters," +
                        " province, cities,region," +
                        " '" + userName + "','" + userName + "','" + userName + "', '" + DateTimeKit.now() + "'," +
                        " '" + treatmentUnit + "','" + customerPhone + "'," +
                        "'" + remarks + "','" + startTimeValue + "','" + endTimeValue + "','" + DateTimeKit.now() + "','9','预约' from reservations_info" +
                        " where id='" + reservationsId + "' limit 0,1";
                sqlMapper.insert(sql);
            }
        }
        //插入视图改动日志 市场预约洁牙
        String sq11 = "INSERT INTO `customer_reservations_update_log` (createtime,single_person,logtype, `userid`, `customer_status`, `customer_name`,`customer_phone`," +
                " `succeed`,outpatient_name ,`message`,customer_id,reservations_id) VALUES " +
                " ('" + DateTimeKit.now() + "','" + userName + "','新增预约','" + userId+ "','预约', '"
                + rinfo.getCustomerName() + "', '" + rinfo.getCustomerPhone() + "','成功','" + outpatientName +
                "','预约项目：" + projectData + " </br>\n预约患者：" + rinfo.getCustomerName() + " </br>\n预约时间：" + startTimeValue.substring(0, 16) + " - " + endTimeValue.substring(11, 16) + " " +
                "</br>\n预约状态：预约 </br>\n预约科室：" + treatmentUnit + " </br>\n预约医生：" + doctor + " </br>\n咨询师：" + fieldTc + " </br>\n备注：" + remarks + "','" + customid + "'," +
                "'" + reservationsId + "');";
        sqlMapper.insert(sq11);
        webSocket.sendMessageViews("保存预约", abbreviation);
        return 1;
    }

    public StoreRecordsInfo getCustomerByReachNew(String reach, String pageStart, String pageSize, String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql2 = " ";
        if (StringUtils.isNotBlank(reach)) {
            sql2 = "  and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') " +
                    "  or t.customer_phone2 like CONCAT('%','" + reach + "','%') or " +
                    "  t.medical_record_number like CONCAT('%','" + reach + "','%')" +
                    " or t.szm like CONCAT('%','" + reach + "','%'))";
        }
        String sql = "select * from (select " +
                "(" +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                " '男' " +
                " WHEN 2 THEN " +
                " '女' " +
                " ELSE " +
                " '未知' " +
                " END " +
                " ) as sex ,t.reservations_id as reservationsId, t.id,t.customer_name as customerName,t.customer_age as customerAge,t.customer_sex_head as customerSexHead," +
                "t.customer_phone as customerPhone,t.customer_phone2 as customerPhone2,t.arrival_store as arrivalStore,t.phone_shar_ship as phoneSharShip," +
                " t.account_arrears as accountArrears,t.medical_record_number as medicalRecordNumber from " + ctable + " t " +
                " where 1=1 " + sql2 + " and arrival_status!=8 and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=9 and arrival_status!=10 " +
                " and arrival_time is not null order by arrival_time desc ) t2 group by t2.reservationsId" +
                " limit 0," + Integer.parseInt(pageStart) * Integer.parseInt(pageSize) + "";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql, CustomerInfo.class);
        String sql1 = "select count(1) from (select id from " + ctable + "  t where 1=1  " + sql2 + " group by reservations_id) t";
        Integer count = sqlMapper.selectOne(sql1, Integer.class);
        StoreRecordsInfo info = new StoreRecordsInfo();
        info.setScordd("dd");
        if (customerInfoList.size() == 0) {//本店查不到，查市场约的患者
            if (StringUtils.isNotBlank(reach)) {
                sql2 = "  and t.search_field like CONCAT('%','" + reach + "','%') ";
            }
            String sqlsc = "select * from (select " +
                    "       ( " +
                    "        CASE t.customer_sex " +
                    "        WHEN 1 THEN " +
                    "        '男' " +
                    "        WHEN 2 THEN " +
                    "        '女' " +
                    "        ELSE " +
                    "        '未知' " +
                    "        END " +
                    "        ) as sex , t.id as reservations_id,t.customer_name as customerName,t.one_channel as oneChannel," +
                    "t.department as department,t.customer_age as customerAge,t.customer_sex_head as customerSexHead,t.first_visit_date as firstVisitDate," +
                    "t.customer_phone as customerPhone,t.customer_phone2 as customerPhone2,t.status,t.arrival_store as arrivalStore," +
                    "t.phone_shar_ship as phoneSharShip from reservations_info t " +
                    " where 1=1 " + sql2 + " ) t2 group by t2.reservations_id order by t2.firstVisitDate " +
                    " limit 0," + Integer.parseInt(pageStart) * Integer.parseInt(pageSize) + "";
            customerInfoList = sqlMapper.selectList(sqlsc, CustomerInfo.class);
            String sql11 = "select count(1) from (select id from reservations_info t where 1=1 " + sql2 + " group by id) t";
            count = sqlMapper.selectOne(sql11, Integer.class);
            info.setScordd("sc");
        }
        info.setCount(count);
        info.setCustomerInfoList(customerInfoList);
        return info;
    }


    public CustomerInfo getCustomerByReservationsId(String reservationsId, String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql = "select " +
                " (" +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                " ) as sex,t.* from " + ctable + " t " +
                " where t.reservations_id='" + reservationsId + "' and t.arrival_time is not null order by t.arrival_time limit 0,1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public CustomerInfo getReservationsByReservationsId(String reservationsId){
        String sql = "select " +
                " ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                " ) as sex,t.* from reservations_info t " +
                " where t.id='" + reservationsId + "' limit 0,1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        return customerInfo;
    }

    public StoreRecordsInfo getCustomerByReach(String reach, String pageStart, String pageSize,String abbreviation){
        String ctable = abbreviation + "customer_info";
        String sql2 = " ";
        if (StringUtils.isNotBlank(reach)) {
            sql2 = "  and (t.customer_name like CONCAT('%','" + reach.trim() + "','%') or t.customer_phone like CONCAT('%','" + reach.trim() + "','%') " +
                    "  or t.customer_phone2 like CONCAT('%','" + reach.trim() + "','%') or " +
                    "  t.medical_record_number like CONCAT('%','" + reach.trim() + "','%')" +
                    " or t.szm like CONCAT('%','" + reach.trim() + "','%'))";
        }
        String sql = "select * from (select " +
                " ( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                " '男' " +
                " WHEN 2 THEN " +
                " '女' " +
                " ELSE " +
                " '未知' " +
                " END " +
                " ) as sex ,t.reservations_id as reservationsId,t.check_status as checkStatus, t.id,t.customer_name as customerName," +
                " t.customer_age as customerAge,t.customer_sex_head as customerSexHead," +
                " t.customer_phone as customerPhone,t.customer_phone2 as customerPhone2,t.arrival_store as arrivalStore,t.phone_shar_ship as phoneSharShip," +
                " t.account_arrears as accountArrears,t.medical_record_number as medicalRecordNumber," +
                " (select ifnull(sum(actual_account_balance),0) from reservemoney_orthodontics_deal t1  " +
                " where t.reservations_id=t1.reservations_id and (now() between t1.recharge_time and t1.end_time) limit 1) as orthodonticsStatus " +
                " from " + ctable + " t " +
                " where 1=1  " +
                "" + sql2 + " and arrival_status!=8 and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=9 and arrival_status!=10 " +
                " and arrival_time is not null order by arrival_time desc ) t2 group by t2.reservationsId" +
                " limit 0," + Integer.parseInt(pageStart) * Integer.parseInt(pageSize) + "";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql, CustomerInfo.class);
        String sql1 = "select count(1) from (select id from " +ctable+ "  t where 1=1  " + sql2 + " group by reservations_id) t";
        Integer count = sqlMapper.selectOne(sql1, Integer.class);
        StoreRecordsInfo info = new StoreRecordsInfo();
        info.setCount(count);
        info.setCustomerInfoList(customerInfoList);
        return info;
    }
}